Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with Sorting and Grouping

As opposed to sorting within forms, sorting the data within a report isn't determined by the underlying query. In fact, the underlying query affects the report's sort order only when no sort order has been specified for the report. Any sort order specified in the query is completely overwritten by the report's sort order, which is determined by the report's Sorting and Grouping window (see Figure 6.24). The sorting and grouping of the report is affected by what options you select when you run a Report Wizard. You can use the Sorting and Grouping window to add, remove, or modify sorting and grouping options for the report. Sorting simply affects the order of the records on the report. Grouping adds Group Headers and Footers to the report.

Adding Sorting or Grouping

Often, you want to add sorting or grouping to a report. To do so, follow these four steps:

NOTE

To remove a sorting or grouping that you have added, click the selector on the line of the field in the Sorting and Grouping window that you want to delete; then press the Delete key. You will be warned that any controls in the Group Header or Footer will be lost.

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 6.26). They are also used to specify details about the grouping options. Here are the sorting and grouping properties:

• Group Header
  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 can be used to display information about the group. For example, if you're grouping by country, the Group Header is used 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
  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 the Group Footer property to display summary information about the group; it's often used to display subtotals for the group.

• Group On
  The Group On property specifies what constitutes a new group. It's often used for situations such as departmental roll-ups. Rather than grouping on the entire department number, you might want to group on the first three digits, for example.

  The Group On choices for text fields are Each Value and Prefix Characters. For Date fields, the choices 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 choices are Each Value and Interval.

• Group Interval
  Used 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 is set to 3, the field's data is grouped on the first three characters.

• Keep Together
  Determines whether Access tries to keep an entire group together on one page. The three choices for the property are No, Whole Group, and With First Detail. The Whole Group option means that Access tries to keep the entire group together on one page. This includes the Group Header, Group Footer, and Detail section. The With First Detail option means that Access prints the group header on a page only if it can also print the first detail record on the same page.

NOTE

If you have set Keep Together 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 detail record is too large to fit on one page, that setting is ignored, too.

What Are Group Header and Footer Properties and Why Use Them?

Each Group Header and Footer has its own properties that determine the behavior of the Group Header or Footer:

• Force New Page
  The Force New Page property can be set to None, Before Section, After Section, or Before & After. When set to None, no page break occurs either before or after the report section. If set to Before Section, a page break occurs before the report section prints; if set to After Section, a page break occurs after the report section prints. When set to Before & After, a page break occurs before the report section prints as well as after it prints.

• New Row or Col
  Determines whether a column break occurs whenever the report section prints. This property applies only to multicolumn reports. The choices 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
  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 set to No, Access prints as much of the section as possible on the current page, inserting each page break 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
  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 do this by changing the value of the report section's Visible property with VBA code, usually on the Format event.

• Can Grow, Can Shrink
  The Can Grow property determines whether the section stretches 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, as it lets you specify whether the group header is repeated on subsequent pages if a report section needs more than one page to print.