Once you have the records that you want in your report, you can decide if you want to sort or group them.
Campers, grouped by camp unit, then T-shirt type, and then size
(from Day Camp database)
A group is a set of sorted records that match a value or interval, like having the same postal code or having a purchase date in the same month or starting with the same letter. You can sort each grouping level differently.
Each group can show a Group Header section, a Group Footer section, both, or neither. In a group header or footer you can put a label for the group or calculate totals with an aggregate function, like Count Records, Count Values, Sum, Avg, Max, or Min. This feature is what makes grouping more powerful than just sorting.
You can group on any field from the source of the report except a Memo/Long Text or Attachment type. You can also use an expression, but not a calculated control.
For example, you could group campers by their unit or grade or age or postal code or by the first letter of their last name. You could group products by price or category or manufacturer or number sold for the month.
Context Menu: Right click a control and choose Group On.
You can also use this menu to sort with this control or to add a total based on it.
Only one sort can be created with the context menu. A second choice replaces the first one.
It is a good idea to open the Group, Sort, and Total pane to see how your choices are fitting together.
Group, Sort, and Total Pane: Click the Add a group or Add a sort button.
A list of available fields pops up. At the bottom of the list is 'expression', which opens the Expression Builder dialog.
Once you have created a group you can edit its properties in the pane.
Using a Calculated Control:
You won't see any of your calculated controls from the report
in the 'Group on' list, only fields from the report's source. You
can copy an expression from a calculated control, open Expression Builder, and paste to use the expression for grouping.
Or, you can change the Control Source for the report to a query that includes the calculation as a column. Then it will show as a choice in the dialog.
Grouping, sorting, and totals are best controlled from a pane that is available only for reports. This is quite different from previous versions of Access. It puts all of the choices in one spot!
The pane gives you total control, unlike using the context menu commands.
Show pane: Group & Sort button on the Report Design Tools: Design ribbon tab.
Hide pane: Click the Group & Sort button again or click the X button at the top right of the pane.
How many groups? You can choose up to 10 fields or expressions to sort or group on.
Order of sorting/grouping? The sorting/grouping is done in the order
of the diagram in the pane, starting with the top. The Design View also shows group headers in this order, but a group does not HAVE to have a group header or footer. The pane tells ALL, if you look.
Change the order with the blue arrows at the right end of the item. You cannot drag an item to change its place in the order.
The order of the groups is VERY IMPORTANT! In the first illustration, records are grouped first by Full Unit name, then by T-shirt type (Adult or Child) and then by T-shirt size (S, M, L, X). In the second illustration, the order was changed to put FullUnit last. The resulting pages are VERY different.
When you change the grouping order, you usually have to adjust the positions of some controls. You might need to remove some Totals and create others. Inspect the Print Preview carefully!
Click on an entry in the Group, Sort, and Total diagram to show its current properties.
Click on More to open a longer list of features. Only one group properties list can be open at a time. To collapse the entry again click on Less or on a different entry in the diagram.
A down arrow beside a property for a group opens a list of choices for the property. There are different choices for different data types.
Totals:
No total (default) or pick a field to 'total'
and pick a function, like Sum or Count Records or Maximum.
Show a Grand Total, which is in the Report Footer by default. Show the group total as a percentage of the Grand Total. Put the subtotal for the group in the header or in the footer or both.
Vanishing Choices: Oddly, the choices you make in the drop list for Totals may not show in the drop list if you return to the pane later. What you are totaling, like 'with AmountPaid totaled', shows in the pane but not which function you picked, like Sum or Count Records, or Maximum. The check boxes are all unchecked when you come back.
In the Star Wars Collectibles database,, a report based on a query groups the items in the collection on the EpisodeName field. This report is an edited version that was originally created with the Report Wizard, choosing to show group totals and a Grand Total.
In
the Group, Sort, and Total pane, there is a single grouping on EpisodeName, which is sorted in alphabetical order and then an additional Sort by ItemName.
Other features: Totaled on AmountPaid. Header and Footer both shown. Grand Total in the Report Footer.
In Report Design View, the group header includes a control for a title for the
group. The group footer
includes a group total and a control that automatically counts the records in the group.
In
the Star Wars Collectibles database, a report groups the records
by EpisodeName and then by CategoryID. Within a category, items are also sorted on ItemName. The order in
the Group, Sort, and Totals pane is VERY important.
In Design View you can see section bars for the headers and footers that will show. But, there is no bar for the CategoryID Footer, which is hidden.
In the Group, Sort, and Total pane you can see what is used to Group or Sort easily. If a group is also sorted, the sort rule shows to the right on the Group line.
To see what Totals are used you must select an item in the diagram and click the More button.
In Print Preview, this report shows group header and footer for EpisodeName (blue background). The group footer has a Total which is the sum of the AmountPaid.
But the CategoryID group shows only a group header, no footer.
It is common to indent the group headers slightly and use different formatting. This helps you to spot the change in the report groupings.
The Grand Total of AmountPaid is in the Report Footer, which is at the end of this one-page report.
![]() |
Step-by-Step: Groups |
![]() |
What you will learn: | to create group |
Start with: , resource files, worldtravel-Lastname-Firstname.accdb from folder databases project4 as updated in the previous lesson
World Travel Inc. wants your Clients Report to show how many clients are in each country. You need to add some groupings and totals to your report. There are only a few clients in the table so far. The more complex the grouping and sorting is, the more records it takes to see if you are getting the arrangement that you wanted.
Currently the Clients Report is not sorting the records at all. The report shows the records in the order in which they were entered in the underlying table. That could be exactly what you want for some reports. For this one, however, you want to see how many clients are from each country. You could sort them on Country and then count by hand. A better way is to group the records so that you can make Access count for you.
Save the report as Clients - by country.
If necessary, click the Group and Sort button on the Report Design Tools: Design ribbon tab to open the pane Group, Sort, and Total at the bottom of the window.
The default choices are OK for most of the properties, but not all.
Click the More button in the item Group on Country to expand the display.
Change the properties to the following:
keep header and first record together on one page
The Design View changes to include the new group footer section. No controls are added automatically to the new sections.
Nothing in the Design View tells you whether the group will be kept together or just header and first record or not, except what is in the Group on Country information in the bottom pane.
Drag the field Country from the Field List and drop it in the Country
Header.
Delete the label for the new control.
If necessary, move the control to the top left of the section.
Save the report.
[Clients - by country]
Headers and footers often benefit from using different background color, fonts, font color, and font size than the Detail section. Divider lines can be helpful, too. You just have to be sure that your divider lines are lined up neatly with each other.
Resizing
lines: Use the Property Sheet to set a line's width or height.
It is difficult to drag a line to resize it without making it slant. Slanted lines often look jagged when printed.
Click the Line tool on the ribbon tab Report Design Tools: Design.
Click in the Country Header section, just
above the Country text box.
A 1 inch line appears, starting where you clicked.
Use the Property Sheet for the line to change the following properties:
Width = 3.5"
The report width enlarges automatically to hold the longer line.
Top = 0"
This puts the line at the top of the section.
Leave the Border Width as Hairline.
With the line still selected, copy the line. (CTRL + C)
Click the Detail section bar, not inside the section.
Paste. (CTRL + V)
A new line appears at the very top of the Detail section.
With the second line still selected, hold the SHIFT key down and drag across the line in the Country Header to select it.
Now both are selected.
Selecting hard-to-see
controls, like lines:
Be sure that Access is set to select with partial enclosure.
(Options > Forms and Reports)
Drag across a part of the area where you
think the control is. Look for handles to appear. If you wind up
selecting too many controls, hold SHIFT down and click on the ones you
don't want to
deselect them.
It can be hard to see that there is a line there when it is right next to the section bar as in the illustration. Once you select it, there are orange borders and handles. Even then it is not as obvious as with a rectangle control.
Next you will fix the problems listed above.
Switch to Report Design View.
When the
report is in good order, save the report.
[Clients - by country]
The report uses 3 pages.
The clients should be sorted alphabetically within each Country group. The control FullName on the report holds the client's name. It is not in the Field List since it is a calculated field. In its right click menu the command Group On is disabled. (Since FullName starts with the first name instead of the last name, it is not suitable for sorting anyway!)
So how can you sort the names? Use the original fields from the report's source.
In Report view, scroll to inspect the report's alphabetizing.
The country names are in alphabetical order but the clients within a country group are not. They are still listed in the order they were entered into the database. Not good!
Switch back to Report Design View.
In
the pane Group, Sort, and Total , click the button Add a sort.
A list of fields from the report's source appears.
Save the report.
[Clients - by country]
No view in Access tells you how many pages there are directly. You can, of course, navigate in Print Preview to the last page. The Navigation Bar in that view shows the page number. BUT, it can take Access a long time to format all of the pages before it can show you the last one.
So, if your report is longer than one page, you would be wise to include page numbers and the total number of pages in the page header or footer. That way you can see the total number of pages on the very first page. Access makes this very easy!
Switch to Report Design View.
From
the ribbon tab Report Design Tools: Design, click on
, the Page Numbers button.
The Page Numbers dialog appears.
Select 'Page N of M'
Select 'Bottom of Page [Footer]'.
Select 'Center'.
Check the box for 'Show Number on First Page'.
Click on OK.
Report Design View now shows a calculated control in the Page Footer
section that uses literal text and two special fields, Page and Pages. The expression is:
="Page " & [Page] & " of " & [Pages]
Switch to Print Preview and inspect the two
pages of the report.
The first page shows "Page 1 of 3" at the bottom of the page.
Problem:
Page numbers are not centered on the paper
This control is not centered on the paper even though you
choose Center alignment in the Page Numbers dialog.
Why: The width for the report is 3.5" but the paper is 8.5" wide. The control IS centered but only within
the width defined in Report Design View. Not a problem after all.
Save the report.
[Clients -by country]
Looking at all pages of this small report, do you notice that something is missing?? There is no title. You may remember for a few days what this report was all about. But what about after a few months? A title that clearly identifies what this report is about is always a good idea!
Unexpected white
space
Adding new sections to a report can
change the way the groups and records fall on the pages. How are your groups set to handle page breaks? If you chose to 'keep whole group together on one page', you
may wind up with a lot more white space than you expected!
Even choosing 'keep header with first record' can result in
a noticeable blank spot if the group header or Detail section are fairly
tall.
Switch to Print Preview.
What changed: The report title shows only on the first page. Adding
this section may have changed the way the report falls on pages. Your report may look different from the illustration, depending on the exact heights of your various sections.
Problem: New Zealand is on the first page unlike the illustration
This might happen if your section heights are slightly shorter than what I used to create the illustration.
The Country group is set to keep the group header with the first detail. After adding the report title, if there is not enough space on the first page for both the next Country group header and the first record, both move over to the second page. That's exactly what the property is designed to do. The exact height of each section plus the page margins determines how much can fit on a page.
Now that your report is looking better, let's get a bit deeper into the groupings. What will change if you have more than one group? Usually you will want to show a header for the new group and include a label in the Group Header.
You will add a group for the PostalCode field. Usually you should have your groups first and then the fields that are just sorted.
Repeat twice more to move it to the second level in the diagram.
(You will need to scroll up to see the Group on Country item.)
There is no change in the Design View. You did not change the order between the two groups, just between a Group and the three Sort items.
The order of the items in the Group, Sort, and Total diagram is very important to what you will see in the report.
Change the following setting for the group PostalCode:
keep whole group together on one page
Navigate to page 2.
Interestingly, New Zealand does not use postal codes.
There is a separate grouping for each of the postal codes for the USA
addresses so the new grouping is working.