Counting/Totaling with Sub-Reports

[ProgEvalJAA]

I have 18 sub-reports, all linked to a master report using region as the linked field.

The master report is grouped on region. The 12 regions are sorted in ascending order.

Each sub-report provides a count of how many customers fall within any one region.

Each region has its own pages due to a force new page before each section as in the Report Detail section’s properties.

So, for region # 1, I have 18 sub-reports as is the case for region # 2, etc. All of the reports have grow/shrink capabilities.

Each region can have anywhere from zero to 20 records listed for each sub-report.

My finished report currently looks something like this:

Region # 1
Question 1
Question 2
Customer Joe Sample
Customer Jane Sample

Number of Customers: 2

Question 3
Question 4
Question 5
Question 6
Question 7
Question 8
Question 9
Question 10
Question 11
Question 12
Question 13
Question 14
Question 15
Question 16
Question 17
Question 18

Note: The number of customers is calculated on each sub-report in the Report Footer in a text box that has the following expression:

I have two questions:

1. First, I want to provide a grand count of records for each geographic region. That is, I want to provide a total of records from report 1 + report 2 + …. In other words, I want the report to look more like this:

Region # 1
Question 1
Question 2
Customer Joe Sample
Customer Jane Sample

Number of Customers: 2

Question 3
Customer Jane Doe

Number of Customers: 1

Question 4
Question 5
Question 6
Question 7
Question 8
Question 9
Question 10
Question 11
Question 12
Question 13
Question 14
Question 15
Question 16
Question 17
Question 18

Total Number of Customers for Region # 1: 3

Region # 2
Question 1
Customer Joe Sample
Customer Jane Sample
Customer John Doe

Number of Customers: 3

Question 3
Customer Jane Doe

Number of Customers: 1

Question 4
Question 5
Question 6
Question 7
Question 8
Question 9
Question 10
Question 11
Question 12
Question 13
Question 14
Question 15
Question 16
Question 17
Question 18

Total Number of Customers for Region # 1: 4

2. After the last geographic region’s pages, I would like a grand total of customers for all geographic regions such as:

Grand Total of Customers for This Time Period: 7

 

[dhookom]

You sample data is wrong. Shouldn't the last "Total Number of Customers for Region..." be region #2 not region #1?

I am having trouble understanding your table structure.

Typically any aggregate total in your report/subreport can be created in the record source query of your main report. This involves creating one or more totals query that generate the numbers you want and then joining them into the record source of the main report.

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

Thank you, dhookum. I created a totals query, turned it into a sbu-report and linked it to my main report using the region as the linked field. That seemed to do the trick for the groups totals.