Sum of a Sum column?

[Zoon]

I have these 3 fields (and others) in an Access 97 database; [Department] a text field, [Quantity] a number field, [Cost] a number field. There are 30 different departments and 9000 records.

This query works and gives the total cost for each department.
SELECT [Department], SUM([Quanty]*[Cost]) AS [Total Cost] FROM [Product]
GROUP BY [Department]

Adding this to SELECT does not work.
SUM(Total Cost) AS [Grand Total].

How can I get the sum of the [Total Cost] column, or the cost of every department?

 

[jrbarnett]

Hi,

Modify your SQL code to:

SELECT [Department], Sum([Quantity]) As [Dept Quantity], Sum([Cost]) AS [Dept Cost]
FROM [department]
GROUP BY [Department]

You can then use sum statements in a report to sum the department quantity and department cost fields, and multiply the overall totals together to give your total cost.

John

 

[MichaelRed]

An alternative is to make the second statement a second query, based on the first. I use the term 'layering' for this approach, although it does not appear to be the current 'popular' term.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places