Subtotals in Query

[AccessExcelHell]

Hi,

Currently, I have this query in access(where I am using totals, group by, etc), which gives me the totals per LOC ID. But then I need to get totals by Area Name, so I copy into Excel to use the Excel Subtotals function. Is there a way to do the same in access?

Region: can have many Area Names (Area Name can belong to only 1 region).
Area Name: can have many Loc IDs (Loc Id can have only 1 area name).

Raw Data:
loc id amount date
5 11 1/1/04
5 45 1/18/04
etc..

Query:
Area Name LocID Jan total Feb total
NY 5 56 18
NY 7 34 82

In Excel:
Region Area Name LocID Jan total Feb total
USA NY 5 56 18
USA NY 7 34 82
USA NY Total 90 100
...........
USA Total 20000 500000
Thanks

 

[bhoran]

You can do the same in Access by building a report, If you use the report wizard you group based on area name and choose the summary area you can then select which fields you wish summarised (I usually choose all and maximum summaries then delete those not necessary) you can then format your report to suit.

This will give you subtotals on Report grouping and grand totals.