Programming Microsoft Access 2000 (Microsoft Programming Series)

One of the major benefits of Design view is that it facilitates grouping and sorting records for display in a report. Two factors underlie this capability: the report bands and the Sorting And Grouping dialog box. The grouping capability simplifies computation of subtotals by group, and it also calculates grand totals.

Figure 6-8 presents a simple report that demonstrates basic sorting, grouping, and calculating functionality for a report. This two-page report shows the report title at the top of the first page only. The label control for the report's title is thus in the Report Header section. In contrast, the two column headings appear in the Page Header section because these headings appear at the top of the columns on both pages. The Detail section contains a pair of text boxes bound to fields in the report's record source. On the report, these text boxes repeat once for each record in the underlying record source. The sort order of these rows can be independent of the order of records in the underlying record source. This capability improves the overall usability of a record sources for multiple reports.

Figure 6-8. A report that shows grouping by date.

The rows in Figure 6-8 are grouped by quarter. You use the Grouping And Sorting dialog box in Design view to select one or more fields on which to group rows. Grouping on a field adds Report Header and Report Footer sections to a report for each new group. This built-in grouping capability automatically adapts to the field data type on which a report groups. For date fields, Access supports grouping by year, quarter, month, week, day, hour, and minute. AutoNumber, Currency, and Number fields enable you to group rows by a custom range. You can group product unit prices in $5 intervals for one report and in $10 intervals for another. You can also group Text fields based on their leading characters. This permits you to create a directory-style report that groups all products beginning with A followed by those beginning with B, and so on.

The built-in grouping capabilities support the calculation of subtotals. Notice that the report in Figure 6-8 summarizes sales orders by month and by quarter. You use an aggregate function, such as SUM, in a group footer section to compute a calculation for the items in a group. Other aggregate functions that can add value to a report include AVG, COUNT, MAX, and MIN. The aggregate function for the subtotal in Figure 6-8 appears in the text box in the Date Footer section. The Control Source expression for the text box is

The argument for this function points at an underlying field for a control in the Detail section. Using txtCountOfOrderID as the argument to point directly at the text box control generates an error.

NOTE