Totaling Issue

[ph00086]

Hi,

I have a report which tells me how many of each part were made, reworked or scraped. At the end of each row there is a column for the number of hits which is the total of all these mulitiplied by a number in a hidden coloumn which is unique to that part. this is done by using

=(Nz([Produced],0)+Nz([Scrap],0)+Nz([Rework],0))*[DoubleHit]

I want to get a total of this coloumn at the bottom of the report similar to the totals for the other coloumns, but when i click the totals tab it only offers me "Count". I've tried using a summing function in a txt box but that doesn't work.

Does anyone have any ideas how i might be able to have a total?

Thanks

 

[Trevor G]

Is this done in the Report Footer if so then make sure the fields have names you can recognise and then add a textfield and in the control source when you click the Ellipse button you should be able to create a sum of the field you want.

If that doesn't work attached a copy of your database to your thread making sure you strip out any sensitive data and make sure the database size is less than 2mg and also zip it. We can take a look and help you out.

 

[vbaInet]

Like Trevor said add it in the Report Footer section and what you add is this:

=[COLOR=Red]Sum([/COLOR](Nz([Produced],0)+Nz([Scrap],0)+Nz([Rework],0))*[DoubleHit][COLOR=Red])[/COLOR]

... as long as none of those fields are calculated.

 

[ph00086]

Tried that and it didnt work, asked me to input the parameter for the summing.

Attached is the database, the report in question is "EL Shipped Tonnes and Hits".

Thanks.

 

[vbaInet]

I don't see where you've put that code in your report. And I forgot to get rid of the Nz() parts in the expression, so please remove those.

 

[ph00086]

I've entered that into the report now,

It gives me a total of the sums in the footer, i need a total of the "Hits" coloumn which is a calculation in itself.

 

[vbaInet]

Alright, I've not looked at your db yet but since you said it's a calculation Sum won't work. You can put the calculation in the Record Source of your report and you will be able to reference that column in the Sum function.

 

[ph00086]

okay, could you please expland on that a bit.

Do i put the calculation used in the coloumn in the record source, and how??

Then just use a normal sum function with the coloumns name

Thanks

 

[vbaInet]

Your report is based on a query right? Copy the calculated Hits code and paste it in a new column in the query. Give the column a meaningful name, go back to your report and refer to that field.

 

[ph00086]

Thanks. that works perfectly.

 

[vbaInet]

Happy to hear.