How to Create Totals & Subtotals in Microsoft Access
Microsoft Access has many built-in tools to customize your reports and increase their usability. Instead of manually adding up values in your report, Access calculates totals and subtotals of the groups. The values will automatically update whenever you add, remove or modify records for your report. You won't need to spend time organizing, sorting or recalculating your fields. After setting up your totals and subtotals, all you'll need to do is print the report and present your information.
Open your report in "Design View" by right-clicking the title in the "Navigation Pane" and selecting "Design View." This mode allows you to make changes to the content, structure and appearance of your report. Click on the "Group & Sort" button on the "Design" tab of the ribbon at the top of the page to make changes to the organization of your report.
Click "Add a Group" to group your records by certain fields. Select the field that will determine your subtotal in the drop-down box next to "Group On." Click on the "More" button to select additional options.
Click "with no totals" and select the check boxes that determine the location of your subtotals. Select "Show Grand Total" to create a totals expression for the whole report. Select "Show Subtotal in Group Header" or "Show Subtotal in Group Footer" to place the subtotal expression on your report.
Click the "Total On" drop-down box to select the field that contains the numbers for your total. Under "Type," select whether you want to count the number of records or the values in that field. Save your report and switch to "Report View" from the "View" tab on the ribbon to review changes.
Tips & Warnings
- You can sort your subgroups by selecting "Add a Sort" beneath the group header in the "Group & Sort" options.