Grouping and Sorting Report Data
Most reports you create require that you organize their data into groups and subgroups in a style similar to the outline of a book. The Report Wizard lets you establish the initial grouping and sorting properties for your data, but you might want to rearrange your report's data after reviewing the Report Wizard's first draft.
The Sorting and Grouping dialog (see Figure 15.1) allows you to modify these report properties in Design mode. This section uses the Products On Hand by Category report that you created in the preceding chapter. The sorting and grouping methods described here, however, apply to any report that you create. To display the dialog, open the report in Design view and click the toolbar's Sorting and Grouping button.
Figure 15.1. The Sorting and Grouping dialog lets you create or alter report groups and the sort order within groups.
The Sorting and Grouping dialog allows you to determine the fields or expressions on which Access is to group the products, up to a maximum of three levels. You can sort the grouped data in ascending or descending order, but you must select one or the other; "unsorted" is not an option. The Sorting and Grouping symbol in the selection button at the left of the window indicates that Access uses the field or expression in the adjacent column to group the records.
Grouping Data
The method that you use to group data depends on the data in the field by which you group. You can group by categories, in which case a unique value must represent each category. You can group data by a range of values, which usually are numeric but also can be alphabetic. You can use the data in a field to group the data, or you can substitute an expression as the basis for the grouping.
Grouping by Category. When you told the Report Wizard to use CategoryID as the field by which to group, you elected to group by category. You can alter the grouping sequence easily by using the Sorting and Grouping dialog. To group by SupplierID, for example, select SupplierID as the first group field. (When you change the group field, Access automatically renames the Group Header and Footer sections.) In the SupplierID Footer section, change the ID text box's Control Source property to the SupplierID field, and rename the text box to txtSupplierID. Next, change the title label in the Report Header to Products on Hand by Supplier, and use the DLookUp function to change the text box's expression to the following:
="Supplier: " & DLookUp("[CompanyName]","Suppliers",
"[txtSupplierID]=Report!SupplierID")
Increase the DLookUp text box's width so that more of the supplier's name is visible in the report. The report appears as shown in Figure 15.2. Choose File, Save As, and save the report under the new name Products On Hand by Supplier.
Figure 15.2. The effect of changing the report grouping so that it displays records by SupplierID.
Note
You cannot let a report's properties or controls limit the number of rows of detail data that a report presents, unless you write a TopN or TopNPercent query using Access SQL. (Search the online Help for the TopValues property to learn more about TopN and TopNPercent queries.) All rows of a table or query appear somewhere in the report's Detail section if the report includes a Detail section with at least one control. To include only a selected range of dates in a report, for example, you must base the report on a query with the criteria necessary to select the Detail records. If the user is to choose the range of records to include in the report, use a parameter query as the report's data source.
◂◂See "Text-Manipulation Functions," p. 310
If you use a systematic code for grouping, you can group by the first five or fewer characters of the code. With an expression, you can group by any set of characters within a field. To group by the second and third digits of a code, for example, use the following expression:
=Mid([FieldName],2,2).
If your table or query contains appropriate data, you can group reports by more than one level by creating subgroups. The Employee Sales by Country report (one of the Northwind Traders sample reports), for example, uses groups (Country) and subgroups (the employee's name—the actual group is an Access expression that combines the FirstName and LastName fields) to organize orders received within a range of dates. Open the Employee Sales by Country report in Design mode to view the additional section created by a subgroup.
Grouping by Range. You often must sort reports by ranges of values. (If you opened the Employee Sales by Country report, close this report and reopen the Products On Hand by Category report in Design mode.) If you want to divide the Products on Hand by Category report into a maximum of six sections—each beginning with a five-letter group of the alphabet (A through E, F through J, and so on) based on the ProductName field—the entries in the Sorting and Grouping dialog should look like the entries in Figure 15.3.
Figure 15.3. Sorting and Grouping criteria to group records in alphabetical intervals.
Access VBA's =Asc([ProductName]) function returns the ASCII (numeric) value of the first character of its string argument, the ProductName field. You set the Group On specification to Interval and then set the Group Interval to 3. This setup groups the data into names beginning with A through C, D through F, and so on (see Figure 15.4). You delete all text boxes in the Group Footer because subtotals by alphabetic groups are not significant. Although of limited value in this report, an alphabetic grouping often is useful for grouping long, alphabetized lists to assist readers in finding a particular record.
Figure 15.4. A report that categorizes products by three-letter alphabetic intervals.
◂◂SeeFunctions
If you group data on a field with a Date/Time data type, Access allows you to set the Sorting and Grouping dialog's Group On property to Year, Qtr (quarter), Month, Week, Day, Hour, or Minute. To group records so that values of the same quarter for several years print in sequence, type the following in the Field/Expression column of the Sorting and Grouping dialog:
=DatePart("q",[FieldName])
Sorting Data Groups
Although most data sorting within groups is based on the values contained in a field, you also can sort by expressions. When compiling an inventory valuation list, the products with the highest inventory value are the most important. The report's users might want these products listed first in a group. This decision requires a record sorting within groups on the expression =[UnitsInStock]*[UnitPrice], the same expression that Access uses to calculate the report's Value column. Figure 15.5 shows the required entries in the Sorting and Grouping dialog.
Figure 15.5. Sorting data by each record on an expression.
The descending sort on the inventory value expression results in the report shown in Figure 15.6. As expected, the products with the highest inventory value appear first in each category.