Information is often easier to understand when it is divided into groups. For example, a report that groups sales by region can highlight trends that otherwise might go unnoticed. In addition, placing totals (such as sums or averages) at the end of each group in your report can replace a lot of manual interaction with a calculator.
Access makes working with grouped reports easy. You can create a basic grouped report by using the Report Wizard, you can add grouping and sorting to an existing report, or you can revise grouping and sorting options that have already been defined.
In this article
Create a quick grouped or sorted report
Even if you’re new to grouped reports, you can quickly create a simple one by using the following procedure:
-
In the Navigation Pane, select a table or query that contains the records you want on your report.
-
On the Create tab, click Report. Access creates a simple tabular report and then displays it in Layout View. If there are many fields in the report, it will probably extend across more than one page. Before applying any grouping or sorting, you might want to resize columns (and delete unwanted columns) so that the report fits on one page width. To delete a column, right-click it and then click Delete Column.
-
Right click a column on which you want to group or sort, and then click Group On [field name] or click one of the Sort options. For example, to group on the Priority column, right-click the Priority column and then click Group On Priority. When applying grouping, Access moves the grouping field to the leftmost column, and groups the remaining columns based on that column. In some cases, Access also adds a grand total to the Report Footer section.
-
Optionally, view and fine-tune your grouping and sorting options by following the procedures in the section, Add or modify grouping and sorting in an existing report.
Build a new grouped report by using the Report Wizard
The Report Wizard presents you with a series of questions, and then generates a report based on your answers. Among those questions is one that asks for the field or fields to use to group your report. After the report is created, you can use it as-is or modify it to better suit your needs. Before you begin with the Report Wizard, you need to decide upon a data source.
Start the Report Wizard
-
On the Create tab, in the Reports group, click Report Wizard.
Access starts the Report Wizard.
-
Click the Tables/Queries drop-down list and choose the table or query that contains the fields you want on your report.
-
Double-click fields in the Available Fields list to choose them.
Access moves them to the Selected Fields list. Alternatively, you can click the buttons located between the Available Fields box and the Selected Fields box to add or remove the selected field or to add all or remove all of the fields.
-
If there are fields in another table or query that you also want to put on your report, click the Tables/Queries drop-down list again and choose the other table or query, and continue to add fields.
-
After you've finished adding fields, click Next.
Group records in the Report Wizard
Grouping lets you organize and arrange records by group, such as by Region or SalesPerson. Groups can be nested so that you can easily identify the relationships among the groups and find the information you want quickly. You can also use grouping to calculate summary information, such as totals and percentages.
When you include more than one table in a report, the wizard examines the relationships between the tables and determines how you might want to view the information.
-
On the page of the Report Wizard that asks Do you want to add any grouping levels?, click one of the field names in the list, and then click Next.
-
To add grouping levels, double-click any of the field names in the list to add them to your report.
You can also remove a grouping level by double-clicking it in the page display on the right side of the dialog box. Use the arrow buttons to add and remove grouping levels, and adjust the priority of a grouping level by selecting it and clicking the up or down priority buttons. Access adds each grouping level and shows it nested within its parent grouping level.
-
Click Grouping Options to display the Grouping Intervals dialog box.
-
Optionally, for each group-level field, choose a grouping interval.
The grouping interval lets you customize how records are grouped. In the previous illustration, records are grouped on the ShippedDate field, which is a Date/Time data type. The Report Wizard offers choices appropriate to the field type in the Grouping intervals list. Thus, because ShippedDate is a Date/Time type, you can choose to group by actual value (Normal), Year, Quarter, Month, Week, Day, Hour and Minute. If the field were a Text data type, you could choose to group by the entire field (Normal), or perhaps by the first one to five characters. For a numeric data type, you can choose to group by value (Normal), or by range in selected increments.
After selecting a grouping interval, click OK.
-
Click Next to navigate to the next page of the wizard.
Sort and summarize records
You can sort records by up to four fields, in either ascending or descending order.
-
Click the first drop-down list and choose a field on which to sort.
You can click the button to the right of the list to toggle between ascending and descending order (Ascending is the default). Optionally, click the second, third, and fourth drop-down lists to choose additional sort fields.
-
Click Summary Options if you want to summarize any of the numeric fields.
Note that the Summary Options button will only be visible if you have one or more numeric fields in the Detail section your report. The wizard displays the available numeric fields.
-
Select the check box under your choice of Sum, Avg, Min or Max to include those calculations in the group footer.
You can also choose to show the details and summary or the summary only. In the latter case, totals for each ShippedDate value are shown (if you selected the check box for Sum, for example), but the order detail is omitted. You can also choose to show percent of total calculations for sums.
-
Click OK.
-
Follow the directions on the remaining pages of the Report Wizard. On the last page, you can edit the title of the report. This title will be displayed on the first page of the report, and Access will also save the report, using the title as the document name. You can edit both the title and the document name later.
-
Click Finish. Access automatically saves the report and displays it in Print Preview, which shows you the report as it will look when printed.
You can use the navigation buttons at the bottom of the preview pane to view the pages of the report sequentially or jump to any page in the report. Click one of the navigation buttons or type the page number that you want to see in the page number box, and then press ENTER.
In Print Preview, you can zoom in to see details or zoom out to see how well data is positioned on the page. With the mouse pointer positioned over the report, click once. To reverse the effect of the zoom, click again. You can also use the zoom control in the status bar.
Add or modify grouping and sorting in an existing report
If you have an existing report and you want to add sorting or grouping to it, or if you want to modify the report's existing sorting or grouping, this section helps you get started.
Add grouping, sorting, and totals
You can perform simple sorting, grouping and totaling operations by right-clicking fields in Layout view and then choosing the operation you want from the shortcut menu. To switch to Layout view, right-click the report in the Navigation Pane and then click Layout view.
Note: Although the instructions in this section don't use the Group, Sort, and Total pane directly, it is a good idea to open the pane and observe how it changes as you work. You will get a better idea of what Access is doing and, as you get more comfortable working with the Group, Sort, and Total pane, you can use it to make additional adjustments to your report. To display the Group, Sort, and Total pane:
-
On the Report Layout Design tab, in the Grouping & Totals group, click Group & Sort.
Sort on a single field
-
Right-click any value in the field on which you want to sort.
-
On the shortcut menu, click the sort option you want. For example, to sort a text field in ascending order, click Sort A to Z. To sort a numeric field in descending order, click Sort Largest to Smallest.
Access sorts the report as you specified. If the Group, Sort, and Total pane is open, you can see that a new Sort by line for the field has been added.
Sort on multiple fields
Note: When you apply sorting by right-clicking a field in Layout view, you can only sort one field at a time. Applying sorting to another field removes the sorting on the first field. This differs from the sorting behavior in forms, where multiple sort orders can be established by right-clicking each field in turn and the choosing the sort order you want. To create multiple sorting levels, see the section Add grouping, sorting, and totals by using the Group, Sort, and Total pane.
Group on a field
-
Right-click any value in the field on which you want to group.
-
On the shortcut menu, click Group On.
Access adds the grouping level and creates a group header for it. If the Group, Sort, and Total pane is open, you can see that a new Group on line for the field is added.
Add a total to a field
This option lets you calculate a sum, average, count, or other aggregate for a field. A grand total is added to the end of the report, and group totals are added to any groups that exist on the report.
-
Right-click any value in the field that you want to total.
-
Click Total.
-
Click the operation you would like to perform: Sum, Average, Count Records (to count all records), Count Values (to count only the records with a value in this field), Max, Min, Standard Deviation, or Variance.
Access adds a calculated text box control to the report footer, which creates a grand total. Also, if your report has any grouping levels, Access adds group footers (if not already present) and places the total in each footer.
Note: You can also add totals by clicking the field that you want totaled and then, on the Design tab, in the Grouping & Totals group, click Totals.
Add grouping, sorting, and totals by using the Group, Sort, and Total pane
Working in the Group, Sort, and Total pane gives you the most flexibility when you want to add or modify groups, sort orders, or totals options on a report. Again, Layout view is the preferred view in which to work because it is much easier to see how your changes affect the display of the data.
Display the Group, Sort, and Total pane
-
On the Report Layout Design tab, in the Grouping & Totals group, click Group & Sort. Access displays the Group, Sort, and Total pane.
To add a new sorting or grouping level, click Add a group or Add a sort.
A new line is added to the Group, Sort, and Total pane, and a list of available fields is displayed.
You can click one of these field names or you can click expression below the list of fields to enter an expression. Once you choose a field or enter an expression, Access adds the grouping level to the report. In Layout view, the display changes immediately to show the grouping or sort order.
For more information about creating expressions, see the article Learn to build an expression.
Notes:
-
If there are already several sorting or grouping levels defined, you may need to scroll down in the Group, Sort, and Total pane before you can see the Add a group and Add a sort buttons.
-
You can define up to 10 grouping and sorting levels in a report.
Change grouping options
Each sorting or grouping level has a number of options that can be set to obtain the results you want.
-
To display all the options for a grouping or sorting level, click More on the level that you want to change.
-
To hide the options, click Less.
Sort order You can change the sort order by clicking the sort order drop-down list, then clicking the option you want.
Group interval This setting determines how the records are grouped together. For example, you can group on the first character of a text field so that all that start with "A" are grouped together, all that start with "B" are grouped together, and so on. For a date field, you can group by day, week, month, quarter, or you can enter a custom interval.
Totals To add totals, click this option. You can add totals on multiple fields, and you can do multiple types of totals on the same field.
-
Click the Total On drop-down arrow and select the field you want to have summarized.
-
Click the Type drop-down arrow and select the type of calculation to perform.
-
Select Show Grand Total to add a grand total to the end of the report (in the report footer).
-
Select Show group totals as % of Grand Total to add a control to the group footer that calculates the percentage of the grand total for each group.
-
Select Show in group header or Show in group footer to display the total in the desired location.
Once all the options have been chosen for a field, you can repeat the process and summarize another field by selecting the other field from the Total On drop-down list. Otherwise, click outside the Totals pop-up window to close it.
Title This allows you to change the title of the field being summarized. This is used for the column heading and for labeling summary fields in headers and footers.
To add or modify the title:
-
Click the blue text following with title.
The Zoom dialog box appears.
-
Type the new title in the dialog box, and then click OK.
With/without a header section Use this setting to add or remove the header section that precedes each group. When adding a header section, Access moves the grouping field to the header for you. When you remove a header section that contains controls other than the grouping field, Access asks for confirmation to delete the controls.
With/without a footer section Use this setting to add or remove the footer section that follows each group. When you remove a footer section that contains controls, Access asks for confirmation to delete the controls.
Keep group together This setting determines how groups are laid out on the page when the report is printed. You may want to keep groups together as much as possible to reduce the amount of page turning that is needed to see the entire group. However, this usually increases the amount of paper needed to print the report, because most pages will have some blank space at the bottom.
-
Do not keep group together on one page Use this option if you are not concerned about groups being broken up by page breaks. For example, a group of 30 items may have 10 items on the bottom of one page and the remaining 20 items at the top of the next page.
-
Keep whole group together on one page This option helps minimize the number of page breaks in a group. If a group cannot fit in the remaining space on a page, Access leaves that space blank and begins the group on the next page instead. Large groups may still span multiple pages, but this option minimizes the number of page breaks within the group as much as possible.
-
Keep header and first record together on one page For groups with group headers, this ensures that the group header will not print by itself at the bottom of a page. If Access determines that there is not enough room for at least one row of data to be printed after the header, the group begins on the following page.
Change the priority of grouping and sorting levels
To change the priority of a grouping or sorting level, click the row in the Group, Sort, and Total pane and then click the up arrow or the down arrow on the right side of the row.
Delete grouping and sorting levels
To delete a grouping or sorting level, click the row you want to delete in the Group, Sort, and Total pane, and then press DELETE or click the Delete button on the right side of the row. When you delete a grouping level, if the grouping field was in the group header or footer, Access moves it to the report's Detail section. Any other controls that were in the group header or group footer are deleted.
Create a summary report (without record details)
If you want to show only totals (that is, just the information in header and footer rows), on the Report Layout Design tab, in the Grouping & Totals group, click Hide Details. Doing this hides the records at the next lower level of grouping, resulting in a much more compact presentation of the summary data. Although the records are hidden, the controls in the hidden section are not deleted. Click Hide Details again to restore the Detail rows to the report.
