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.
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:
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.
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.
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.
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.
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.
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.
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 Build an expression.
Each sorting or grouping level has a number of options that can be set to obtain the results you want.
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.
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:
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.
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.
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.
If you want to show only totals (that is, just the information in header and footer rows), on the 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.
Top of page