Report Subtotal and Totals with Averages and Sums

[GMasterLi]

Hi - new to forums, new Access user. Trying to create a "totals" summary report, with subtotals based both on averages and sums. Illustrative example below. I have tried creating this report based on 1) a "full" query with all of the underlying data, and 2) a "totals" query. With 1) I can get the grant totals to display, but can't get the subtotals to collapse. With 2) I can get the subtotals to display, but can't get the grand total to display.

Preference is to base report on the totals query - but really don't care as long as i can get the right display. Help!

Country	Average Age	Average Weight	Sum of Purchases (USD)
Canada	34 (subtotal avg)	180 (subtotal avg)	10,000 (sum of Canada)
USA	35 (subtotal avg)	190 (subtotal avg)	15,000 (sum of USA)
UK	40 (subtotal	170 (subtotal avg)	8,000 (sum of UK)
Totals	36.5 (Total avg)	185 (total avg)	33,000 (total Sum)

[June7]

Did you try basing report on raw data and using Grouping & Sorting with aggregate calcs in group and report footers?

[GMasterLi]

Hi yes - I tried based on the raw data, using exactly that - the grouping and totals function in the reports - but it isn't collapsing the categories into subtotals. So to illustrate - it doesn't collapse "country" so that i get the aggregate data by sub-group (e.g. Canada, USA, UK)

Country	Weight
Canada	120
Canada	150
Canada	140
USA	170
USA	104

[June7]

So you don't want to show the detail records? Options:

1. Set Detail section visible property to No

2. Base the report RecordSource on a GROUP BY query that sums and averages on the country. These summary records will be the 'raw' details for the report then do grand total sum in report footer.