Unlike sorting data within a form, sorting data within a report isn't determined by the underlying query. In fact, the underlying query affects the report's sort order only when you have not specified a sort order for the report. Any sort order specified in the query is completely overwritten by the report's sort order, which is determined in the report's Sorting and Grouping window (see Figure 17.23). The sorting and grouping of a report is affected by what options you select when you run the Report Wizard. You can use the Sorting and Grouping window to add, remove, or modify sorting and grouping options for a report. Sorting simply affects the order of the records in the report. Grouping adds group headers and footers to the report.
Often, you want to add sorting and grouping to a report. Grouping allows you to add group headers and group footers to a report, and sorting allows you to designate the sort order within your groups. To add grouping and sorting, follow these four steps:
Click Sorting and Grouping on the Report Design toolbar to open the Sorting and Grouping window.
Click the selector of the line above where you want to insert the sorting and grouping level. In Figure 17.24, a sorting and grouping level is being added above the City grouping. Press the Insert key on the keyboard to insert a blank line in the Sorting and Grouping window.
Click in the Field/Expression column and use the drop-down list to select the field on which you want to sort and group.
Set the properties to determine the nature of the sorting and grouping (see the next section, "Sorting and Grouping Properties"). Close the Sorting and Grouping window, if desired.
To remove a sorting or grouping that you have added, you click the selector on the line of the field in the Sorting and Grouping window that you want to delete; then you press the Delete key. Access warns you that any controls in the group header or footer will be lost.
Each grouping in a report has properties that define the group's attributes. Each group has five properties that determine whether the field or expression is used for sorting, grouping, or both (see Figure 17.25). They are also used to specify details about the grouping options. The following sections describe the sorting and grouping properties.
The Group Header property specifies whether the selected group contains a header band. When you set the Group Header property to Yes, an additional band appears in the report that you can use to display information about the group. For example, if you're grouping by country, you can use the Group Header property to display the name of the country you're about to print. If the Group Header and Group Footer properties are both set to No, the field is used only to determine the sort order of the records in the report.
The Group Footer property specifies whether the selected group contains a footer band. When you set the Group Footer property to Yes, an additional band appears in the report. You can use this band to display summary information about the group; it's often used to display subtotals for a group.
The Group On property specifies what constitutes a new group. It is often used for situations such as departmental roll-ups where you roll several subdepartments into a summary department. Rather than group on the entire department number, you might want to group on the first three digits, for example.
The Group On settings for Text fields are Each Value and Prefix Characters. For Date fields, the settings are much more complex. They include Each Value, Year, Qtr, Month, Week, Day, Hour, and Minute. This means you could group by a Date field and have Access subtotal and begin a new group each time the week changes in the field. For AutoNumber, Currency, and Number fields, the settings are Each Value and Interval.
You use the Group Interval property with the Group On property to specify an interval value by which data is grouped. If, for example, the Group On property for a Text field is set to Prefix Characters, and the Group Interval property is set to 3, the field's data is grouped on the first three characters.
The Keep Together property determines whether Access tries to keep an entire group together on one page. The three settings for the property are No, Whole Group, and With First Detail. Setting this property to Whole Group causes Access to try to keep the entire group together on one page. This includes the group header, the group footer, and the Detail section. Setting this property to With First Detail causes Access to print the group header on a page only if it can also print the first detail record on the same page.
If you have set the Keep Together property to Whole Group and the group is too large to fit on a page, Access ignores the property setting. Furthermore, if you set Keep Together to With First Detail and either the group header or the detail record is too large to fit on one page, that setting is ignored, too.
Each group header and footer has its own properties that determine the behavior of the group header or footer (see Figure 17.26). The following sections describe these properties.
You can set the Force New Page property to None, Before Section, After Section, or Before & After. If it is set to None, no page break occurs either before or after the report section. If it is set to Before Section, a page break occurs before the report section prints; if it is set to After Section, a page break occurs after the report section prints. If it is set to Before & After, a page break occurs before the report section prints as well as after it prints.
The New Row or Col property determines whether a column break occurs whenever the report section prints. This property applies only to multicolumn reports. The settings are None, Before Section, After Section, and Before & After. Like the Force New Page property, this property determines whether the column break occurs before the report section prints, after it prints, or before and after, or whether it's affected by the report section break at all.
The Keep Together property specifies whether you want Access to try to keep an entire report section together on one page. If this property is set to Yes, Access starts printing the section at the top of the next page if it can't print the entire section on the current page. When this property is set to No, Access prints as much of the section as possible on the current page, inserting page breaks as necessary. If a section exceeds the page length, Access starts printing the section on a new page and continues printing it on the following page.
The Visible property indicates whether the section is visible. It's common to hide the visibility of a particular report section at runtime in response to different situations. You can easily accomplish this by changing the value of the report section's Visible property with a macro or VBA code, usually on the Format event.
The Can Grow property determines whether you want the section to stretch vertically to accommodate the data in it. The Can Shrink property specifies whether you want the section to shrink vertically, eliminating blank lines.
The Repeat Section property is a valuable property that lets you specify whether Access repeats the group header on subsequent pages if a report section needs to print on more than one page.