Advanced Access Reports: Grouping, Totals, and Subreports

Basic Access reports are easy to create with the Report Wizard. But the real power of the Access report designer comes from its advanced features — grouping, running totals, conditional formatting, and subreports. These tools let you produce professional, publication-quality output directly from your database.

Understanding Report Sections

Every Access report is divided into sections, each of which prints at a specific point:

• Report Header — prints once at the very beginning of the report
• Page Header — prints at the top of every page
• Group Header — prints once at the start of each group (if grouping is defined)
• Detail — prints once for every record
• Group Footer — prints once at the end of each group
• Page Footer — prints at the bottom of every page
• Report Footer — prints once at the very end of the report

Understanding which section to place a control in is the key to building complex reports correctly.

Grouping and Sorting

Grouping organizes your report into logical sections. For example, grouping an orders report by customer shows all orders for each customer together, with a header and footer for each customer group.

To add grouping:

1. Open the report in Design View
2. Click Group & Sort on the Design tab
3. Click Add a group and select the field to group by
4. Choose sort order (ascending or descending)
5. Expand the group options to set header/footer visibility and grouping interval

Grouping intervals are especially useful for date fields. Instead of grouping by exact date, you can group by week, month, quarter, or year.

Aggregate Functions in Group Footers

The Group Footer section is where you put summary calculations for each group. Access provides these aggregate functions for use in report text boxes:

• =Sum([Amount]) — total of all Amount values in the group
• =Count([OrderID]) — count of records in the group
• =Avg([Amount]) — average value
• =Max([Amount]) — highest value
• =Min([Amount]) — lowest value

Place a text box in the Group Footer and set its Control Source to one of these expressions. The calculation automatically applies to the current group's records.

Running Totals

A running total accumulates across records — each row shows the sum of all previous rows plus the current one. This is useful for balance sheets, cumulative sales, and inventory running totals.

To create a running total:

1. Add a text box to the Detail section
2. Set its Control Source to the field you want to accumulate (e.g., =[Amount])
3. Set the Running Sum property to:
   • Over Group — resets to zero at the start of each group
   • Over All — accumulates across the entire report

Conditional Formatting

Conditional formatting changes the appearance of a control based on its value — highlighting overdue dates in red, showing negative numbers in a different color, or bolding records that exceed a threshold.

To add conditional formatting:

1. Select the control in Design View
2. Click Format → Conditional Formatting on the ribbon
3. Click New Rule
4. Set the condition (field value, expression, or comparison to another field)
5. Set the formatting to apply (font color, background color, bold, italic)

You can add multiple rules with different conditions. Rules are evaluated in order — the first matching rule wins.

Example: Highlight orders over $10,000 in green:

• Condition: Field Value is greater than 10000
• Format: Background color = green

Page Breaks and Keep Together

For professional reports, controlling where page breaks occur is important. You do not want a group header at the bottom of a page with the detail records on the next page.

Keep Together property on a group: Set to "With First Detail" to ensure the group header always appears on the same page as at least the first detail record.

Force New Page property on a section: Set to "Before Section" to start each group on a new page — useful for customer statements or invoices where each customer gets their own page.

Subreports

A subreport is a report embedded inside another report, similar to how subforms work in forms. Use subreports when you need to show related data from a different record source within the same printed output.

Common use case: A customer statement report that shows customer details in the main report and a list of their invoices in a subreport.

To add a subreport:

1. Open the main report in Design View
2. Click the Subform/Subreport control in the Controls group
3. Draw it in the section where you want it to appear
4. Use the wizard to select the source report or table
5. Set the Link Master Fields and Link Child Fields (same concept as subforms)

Calculated Fields in Reports

You can add calculated fields directly in the report without modifying the underlying query:

=[UnitPrice] * [Quantity]
=[LastName] & ", " & [FirstName]
=DateDiff("d", [OrderDate], Date())

Place a text box in the Detail section and set its Control Source to the expression. The calculation runs for each record.

Exporting Reports

Access reports can be exported to multiple formats:

• PDF — File → Export → PDF or XPS (best for sharing)
• Word — preserves formatting as a Word document
• Excel — exports data in a grid format
• HTML — for web publishing
• Snapshot — Access's own read-only format

For automated exports from VBA:

DoCmd.OutputTo acOutputReport, "MonthlyOrders", acFormatPDF, "C:\Reports\MonthlyOrders.pdf"

Conclusion

The Access report designer is a genuinely powerful tool that most users barely scratch the surface of. Grouping, running totals, conditional formatting, and subreports give you the building blocks for professional, publication-quality output. Invest time in learning these features and you will be able to produce reports that would take hours to build manually in Word or Excel — generated automatically from live data with a single click.