Lesson · 22 min

Creating Reports

Build reports that organize data into groups with totals, and print correctly on the page.

By the end of this lesson you can

Generate a report and adjust it for printing
Group and sort report data
Add totals in the correct report sections
Place headers and page numbering where they belong

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Building the report

Worked example

A grouped supply report that prints correctly

Produce a report grouped by category with subtotals, a grand total, and headings on every page.

1
Base the report on a query, not the table.
The query is where filtering, sorting, and calculated fields belong. A report built directly on a table has to do that work in the report itself, which is harder to change and cannot be reused.
2
Use Group & Sort to group by Category, then sort by ItemName within it.
Grouping creates the Group Header and Group Footer sections you need for headings and subtotals. Without a group there is nowhere to put a subtotal, which is why people end up doing it by hand.
3
Put the report title in the Report Header and the column headings in the Page Header.
This is the distinction people get wrong, and it only shows up on page two. The Report Header prints once; the Page Header prints on every page. Put headings in the Report Header and pages two onward are unlabeled columns of numbers.
4
Add =Sum([TotalCost]) to the Group Footer, and the same expression to the Report Footer.
The identical expression means different things depending on which section it sits in — the Group Footer totals that group, the Report Footer totals everything. Nothing about the expression tells you which; only its position does.
5
Set Force New Page on the Group Header if each category should start its own page.
Useful when the report is distributed by department, so each recipient gets whole pages. Leave it off for a report someone reads end to end, where it just wastes paper.
6
Check it in Print Preview, not Report View.
Report View does not paginate. A report that looks correct there can still run a column off the page edge, and the only way to see that is Print Preview.

Result: A report with headings on every page, subtotals per category, a grand total, and nothing running off the paper.

Build on a query, group before you total, title in the Report Header and headings in the Page Header, and check in Print Preview.

A report is organized for a reader

On the job

You produce the quarterly supply report at Lakeside Medical Associates.

Your first attempt is 340 rows in entry order across eleven pages, with column headings only on page one. It contains every fact the manager asked for and answers none of her questions, because nothing is organized and pages two onward are unlabeled columns of numbers.

Your task: Use grouping, sorting, and totals to turn a list into something someone can use.

Section	Prints	Put here
Report Header	Once, at the very start	Title, date range, practice name
Page Header	Top of every page	Column headings
Group Header	Before each group	The group's name
Detail	Once per record	The record's fields
Group Footer	After each group	Subtotals for that group
Page Footer	Bottom of every page	Page numbers
Report Footer	Once, at the very end	Grand totals

Report sections and what belongs in each

The Page Header versus Report Header distinction is the one people get wrong. A title in the Page Header repeats on all eleven pages; column headings in the Report Header appear only on page one, leaving the rest unlabeled. Title goes in the Report Header, column headings in the Page Header.

Totals are added by placing a text box containing an expression such as =Sum([TotalCost]) in the appropriate footer. In the Group Footer it totals that group; in the Report Footer it totals everything. The same expression means different things depending only on where it sits.

Check your understanding

Where should column headings go so they appear at the top of every printed page?

Challenge

Apply what you've learned in this lesson.

Build a report someone could take into a meeting.

Build a report on your supplies data grouped by category and sorted by item name within each group.
Put a title in the Report Header, column headings in the Page Header, and page numbering in the Page Footer. Verify in Print Preview across at least two pages.
Add a subtotal in the Group Footer and a grand total in the Report Footer. Confirm the subtotals sum to the grand total.
Adjust the layout until nothing runs off the page, then write two sentences on what you changed.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

← Creating Forms for Data Entry