sum total in a query

[dragginthru]

I have a query which returns charge_cost (based on course cost, whether it went ahead, if hospitals are eligible for charging etc) which is then used in an existing report.

I want to make another report which simply is:

Total training spend for 2004-2005:
£1276.04

And i just want that to be the SUM of charge_cost.

I cant work out how to do this - i did a new query including charge_cost and then created a new field called total_spend: sum([charge_cost]) but i keep getting the message "You tried to execute a query which does not include the specified expression charge_cost as part of an aggregate function"

I have no idea what this means...

 

[neileg]

You need a totals query. In query design view click the sigma Σ button.

 

[Pat Hartman]

Let me explain this further. The sigma button will add a new line to the QBE grid and fill it with the value "group by" for all selected fields. You need to change group by to sum for the fields you want to sum. Make sure you don't include too many fields in the query or it won't aggregate properly.