Sum Total In a Query

[brandys]

Hi I sure hope that someone can help me.

I have a query that has the following fields:

Date, Department Name, Code, Supply Type, Account#,Quantity Delivered, Price Per Item, (calculated)Total Price.

These fields are for a end of month report for an inventory database. I need to be able to pull each department its code, the supply type and account number and the total Total amount spent by that department for each type of supply (ex) medical, office, dietary, other. The problem that I am having is that I can not get a sum tota for each supply type. I have tried using the sum function in the query but all I get is the total for one field not for all. If anyone can help I would be very gratefull. I have tried everything that I know. Thank you.

 

[annsolomon]

Set the Total field in your query design grid to GroupBy for Department Name, Code, Supply Type, Account#.

Set the Total field to Sum for Quantity Delivered and Price Per Item.

Create an expression for your (calculated)Total Price field (e.g., Ext: Sum([Quantity Delivered]*[Price Per Item]) and set the Total field to Expression.

This will group as you want on your report. Then you can add controls on your report to calculate grand totals for each department and grand total for the entire report.
Ann