Return total of totals?

[Fred]

Hi guys, I would like to know if this is possible. I have a price column, in
my query I return this as a total row, so it shows the totals for each item I
listed in my query. I want to know if it's possible to also have a final row
or column that shows that grand total for each total returned. Thanks.

 

[John Spencer]

You can do this in a report.

You can do this using a Union query where you eliminate the grouping a second
query that returns the totals

SELECT 1 as Detail, Product, Sum(Price) as TotalPrice
FROM SOMETABLE
GROUP BY 1, Product
UNION ALL
SELECT 2, "Total", Sum(Price)
GROUP BY 2, "Total"
ORDER BY Detail, Product

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Fred]

Perfect. Thanks John!

You can do this in a report.

You can do this using a Union query where you eliminate the grouping a second
query that returns the totals

SELECT 1 as Detail, Product, Sum(Price) as TotalPrice
FROM SOMETABLE
GROUP BY 1, Product
UNION ALL
SELECT 2, "Total", Sum(Price)
GROUP BY 2, "Total"
ORDER BY Detail, Product

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County