Sums depending on grade

[optidisk]

I have created a logging report that shows the records from a form, the fields are Species, Length, CDUB, RAD, VOL, Grade
I need to sum the VOL of each Grade, so if a row has the grade "O" include it in the sum. there are 5 different Grades so I will need 5 formulas.
I tried this =Sum(IIf([Grade]="O",1,0)), but it produces an error.
Cheers
optidisk
Australia

 

[KenHigg]

Maybe you could try to do your iif()'s in the underlying query, before it gets to the report...

 

[optidisk]

I'll give that a go, thanks
optidisk

 

[optidisk]

I tried this in a select query - Quota: Sum(IIf([Grade]="O",[VOL],0)) and I now recieve the error msg "You Tried To Execute a query that does not include the specidied expression "ID" as part of the aggregate function"
ID is in the query but not needed for the function, what does this mean?
cheers
optidisk

 

[KenHigg]

Do the sum in the report...

(I think this will work...)

 

[optidisk]

still not working

Thanks for all the tips everyone but unfortunately if I put the iif sum in the report footer where it is wanted I get Error. As for the select query, you've lost me there - the underlying select query for the report is made up of five fields from 2 tables and two calculation fields created in the query, these two fields are RAD and VOL (VOL being the one I want to some depending on Grade) Is this the problem, that I am summing a calculated field depending on Grade type?

Cheers
optidisk

 

[Rich]

Are these grades a numeric value? If so remove the quotes around the "0"

 

[optidisk]

No they are text - (milling grade of timber)
I have sort of got it working by using hidden fields in the detail of the report in the calculation fields, only problem even after setting the hide duplicates value to yes, I still get one repeat of the totals on a new page.

 

[Brianwarnock]

I tried this in a select query - Quota: Sum(IIf([Grade]="O",[VOL],0)) and I now recieve the error msg "You Tried To Execute a query that does not include the specidied expression "ID" as part of the aggregate function"
ID is in the query but not needed for the function, what does this mean?
cheers
optidisk

The use of sum turns your query into a totals query just as surely as if you had selected it from the query grid toolbar and thus all selected fields must have an aggregate function eg Groupby Count etc.
To do what you want by queries then you may need to use multiple queries if you cannot use aggregate functions on all selected fields.

Brian

 

[Rich]

You did say the Report footer, however it appears you're using the Page Footer, which one is it that you want the totals to appear in?

 

[optidisk]

The page footer

 

[Rich]

You cannot use the Sum function in the page footer
Article ID: Q132017

 

[optidisk]

The only way I got it working was to put the sum functions in the detail section and hide duplicates. I tried every section in the report and having it in the detail section gave the best results for the printout
cheers
optidisk

 

[optidisk]

Here's what I have so far

I have attached the database, the problem reports are the three different types of invoices.