Calculating group subtotals and report totals
If you use the Report Wizard to create a report, and you use the Summary Options button to request sums, averages, minimum values, or maximum values for each group, you already have subtotals and totals on your report. But you can make them yourself in Design view, too.
After you group your report on one or more fields, you can add subtotals. In the group footer section, create a text box control for each sum, count, or other summary information that you want to print. To print totals and counts for the entire report, make a text box in the Report Header or Report Footer section. Then type an expression in the Control Source property for the text box, using aggregate functions such as Sum(), Avg(), and Count().
When you use aggregate functions in a group header or footer section, Access automatically restricts the records to those in the current group. For example, the Sum() function totals the values of a field for all the records in the group. To subtotal the amount paid for each product in the current group, you use the following expression in a text-box control:
= Sum([Price])
Tip: To print the number of records in the report, type the following expression in the Control Source property (located on the Data tab of the Property sheet) for a text box in the Report Header or Report Footer section:
= Count(*)
Don't use aggregate functions in the Page Header or Page Footer sections of a report; you get an #Error message.
In this tutorial:
- Creating and Editing Reports
- What are Forms and Reports
- Creating Reports Automatically
- Running the Report Wizard
- Creating Simple Reports
- Editing Reports in Design View
- Adding page headers, footers, and numbers
- Adding sections that group your records
- Sorting the records in your report
- Calculating group subtotals and report totals
- Formatting Tips and Tricks
- Copying Forms to Reports
- Adding and Formatting Subreports
- Displaying Empty or Long Fields
- Creating Mailing Labels