Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 36 ยท Forms and Reports

Grouping, Sorting, Totals and Running Sums in Access Reports

Use the Group, Sort and Total pane in Access reports: add group levels, subtotals and grand totals, percentages, running sums, keep-together, page breaks and subreports.

The real power of a report is how it arranges rows. Instead of a flat list of every order, you can show orders grouped by customer, customers grouped by country, with a subtotal under each group and a grand total at the end. That structure turns a pile of data into something a person can read in half a minute.

This lesson works with the Group, Sort and Total pane and the sections it creates, then covers percentages, running totals, page breaks and subreports. It assumes you can already create a basic report from Creating Reports in Access.

The Group, Sort and Total pane

Open a report in Layout view or Design view. On the Design tab (Report Design Tools), click Group & Sort. A pane appears at the bottom of the window with two buttons: Add a group and Add a sort.

  1. Click Add a group and pick a field, for example CustomerName.
  2. Access adds a Group Header (and optionally a Group Footer) for that field.
  3. Click Add a sort to sort the detail rows within each group, such as by OrderDate.
  4. Click the group line's More > to reveal options.

Group levels are listed top to bottom. The first is the outermost grouping. Order matters: grouping by Country then City is different from City then Country. Each group line has a sort order (ascending, descending), and the sorting inside each group comes from the Add a sort lines listed below it.

Group levels nest

Country First group level
City Second level, inside each country
Orders Detail rows inside each city

Each level can have its own header and footer.

1 / 2

The More options

Click More on a group line to set:

Option Meaning
with a header section / without a header section Show or hide the Group Header.
with a footer section / without a footer section Show or hide the Group Footer, where subtotals go.
do not keep group together on one page Choose keep whole group together on one page or keep header and first record together to avoid orphaned headings at the bottom of a page.
Group on Choose how values form groups. For text: Each Value, Prefix Characters (group by first letter). For dates: Day, Week, Month, Quarter, Year. For numbers: Interval.
with Totals Add Sum, Count, Average, Maximum and Minimum, choose the field, and optionally show a grand total, group percentage and subtotals.
with title Add a title label for the group.

The Totals menu is a fast way to add subtotals without writing expressions. Pick the field, pick the type, tick Show subtotal in group footer and Show Grand Total. Access places a text box in the proper footer with the right =Sum(...) expression.

Grouping on dates

Grouping on a date field normally groups by every distinct value. To group by month, open More and change by entire value to by month. Access sets a group on Format(OrderDate,"yyyy-mm") behind the scenes. Alternatively, put a calculated field in the report's query, such as SaleMonth: Format([SaleDate],"yyyy-mm"), and group on it. This is more predictable, and you can sort the query itself.

Subtotals and grand totals by hand

You can type any aggregate expression in a text box in a footer section. Place the text box in the Group Footer, set its Control Source to an expression and give it a name:

Text
=Sum([LineTotal])
=Count(*)
=Avg([UnitPrice])

The same expression in the Report Footer gives the grand total; in a Group Footer it gives the subtotal for the group; in the Page Footer it gives the page total. Aggregates are evaluated within the section's scope, which is why the same formula gives different numbers in different places.

A subtle point: an aggregate such as =Sum([LineTotal]) must use a field from the record source. It cannot sum another control on the report. If you need to sum a calculated control txtLineTotal, either compute the value in the query or repeat the expression: =Sum([Quantity]*[UnitPrice]).

Here is the equivalent of grouping and subtotals in SQL, using GROUP BY ... WITH ROLLUP-style results built with UNION ALL. The report engine does this work for you, but seeing the numbers helps you check a report:

SQL Subtotals per customer and a grand total

Press Run (or Ctrl + Enter) to see the result here.

Percentage of the total

A common request is "what share of the grand total is each customer?" In a report, divide the group's sum by the report-level sum. Access lets you refer to a total in a higher section by the name of the text box that holds it:

Text
=[txtGroupTotal]/[txtGrandTotal]

Format the control as Percent. The grand total text box lives in the Report Footer, which prints last, but Access can still use it in a Group Footer text box. Alternatively, wizard-made totals have an option Show group totals as % of grand total.

The same calculation in SQL uses a window function. SQLite supports them, and so does SQL Server (where an Access front end would normally link):

SQL Each customer's share of the total

Press Run (or Ctrl + Enter) to see the result here.

Running sums

A running sum adds each detail row to a total so far: invoice balance after each payment, or cumulative sales through the month. Select a text box in the Detail section, open its Property Sheet, and set Data > Running Sum:

  • No: normal behavior.
  • Over Group: starts again for each group.
  • Over All: runs across the entire report.

Bind the text box to the field (or expression) you want to add up. To show cumulative sales per day, put Amount in the text box and set Running Sum = Over All. If you set Over Group, the sum restarts at each new customer or month.

SQL A running total

Press Run (or Ctrl + Enter) to see the result here.

You can also number the detail rows: a text box with Control Source =1 and Running Sum = Over Group counts 1, 2, 3 within each group.

Controlling page breaks and spacing

  • Force New Page property on a group header, footer or detail: Before Section, After Section or Before & After. A common setting is Force New Page = After Section on a Group Footer, so each customer starts on a fresh page, which is ideal for statements.
  • Keep Together on a section prevents it from splitting between pages.
  • Repeat Section on a group header repeats it at the top of a new page if the group continues.
  • Can Grow and Can Shrink on text boxes let long text wrap without clipping, while Hide Duplicates (set on a text box) suppresses a value identical to the one above it.

Use Hide Duplicates = Yes on a group field in the Detail section as a cheap alternative to a Group Header when you want a compact "stepped" look.

Alternate row colors

On the Format tab, Alternate Back Color shades every second row of the Detail section. Choose a light grey for readability, and make sure it prints well on a black-and-white printer.

Subreports

A subreport is a report inside another, just like a subform. Use it to show a list under each record, such as the invoice lines under each invoice header, or a second summary beside the first. Drag a report from the Navigation Pane onto the Detail section of the main report, or use the Subform/Subreport control. As with subforms, you set Link Master Fields and Link Child Fields.

Subreports are slower than grouping, because Access runs a separate query for each main record. Prefer groups when a single query will do; use subreports when the two lists come from different sources or have different layouts.

Conditional formatting and expressions

Just as on forms, Format > Conditional Formatting colors cells by rule: bold red for overdue balances, green for goals met. Highlights that depend on group totals need the text box to live in the matching footer.

A worked example

Suppose you want an "orders by country and customer" report:

  1. Create a query with Country, CustomerName, OrderDate, OrderID and LineTotal.
  2. Run Create > Report Wizard, add all the fields, group by Country then CustomerName, sort by OrderDate, and choose Sum on LineTotal with the Summary Options.
  3. Choose Stepped layout and Landscape if wide.
  4. Open in Layout view, widen the columns and format LineTotal as Currency.
  5. In Design view, add ="Page " & [Page] & " of " & [Pages] in the Page Footer and a title in the Report Header.
  6. Set Force New Page = After Section on the Country group footer if each country should print separately.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where do you put a text box that shows the subtotal for each customer?

    A Sum expression in a Group Footer is evaluated for just that group, so it shows the group's subtotal.
  2. 2. What does Running Sum = Over Group do?

    Over Group restarts at each group; Over All keeps adding across the entire report.
  3. 3. Which option makes each customer's section start on a fresh page?

    Force New Page starts a new page before or after a section, which suits statements.
  4. 4. How do you group a report's date field by month?

    The group's Group on setting offers Day, Week, Month, Quarter and Year for date fields.

Summary

  • The Group, Sort and Total pane adds group levels, sorts and totals; group order sets the nesting.
  • Put aggregates such as =Sum([LineTotal]) in group footers for subtotals and in the Report Footer for grand totals.
  • Compute percentages by dividing a group total by the grand total; use Running Sum for cumulative amounts.
  • Control page flow with Force New Page, Keep Together and Repeat Section; use Hide Duplicates for compact layouts.
  • Use subreports for lists from separate sources, and groups when one query will do.

Next: Macros in Access