Working with Sorting and Grouping

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.

Adding Sorting and Grouping to a 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:

Sorting and Grouping Properties

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.

Group Header

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.

Group Footer

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.

Group On

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.

Group 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.

Keep Together

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.

Group Header and Footer Properties and Why to Use Them

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.

Force New Page

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.

New Row or Col

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.

Keep Together

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.

Visible

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.

Can Grow and Can Shrink

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.

Repeat Section

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.