Defining the Grouping and Sorting Criteria

The next thing you need to do is define the grouping and sorting criteria for the report. Click the Group & Sort button in the Grouping & Totals group on the Design tab to open the Group, Sort, And Total pane. This report should display the daily reservation data from the query in the Detail section, with summaries of reservations by date, by month, and by facility. Note that in the Group, Sort, And Total pane, you specify grouping values from the outermost to the innermost (like specifying a sorting criteria left to right). So, select the FacilityName sorting specification in the first line of the Group, Sort, And Total pane, click More to expand the options, click the arrow on the Without A Footer box, and then click With A Footer Section. Notice that when you add a group header or group footer for any field or expression in the Group, Sort, And Total pane, Office Access 2007 adds an appropriate section to your report. Access also changes this specification from Sort By to Group On. You want to make sure that a group header doesn’t get “orphaned” at the bottom of a page, so click the arrow on the option that says Do Not Keep Group Together On One Page and click Keep Header And First Record Together On One Page. Note that you can also ask Access to attempt to keep all the detail for this level of grouping on one page by clicking the Keep Whole Group Together On One Page option. When you do this, Access will produce a new page if all the detail for the next group won’t fit on the current page. As you’ll see later, the report sections also have properties that you can set to force a new page with the start of each group.

The DateValue field from the query returns the date each room is occupied across a reservation span. When housing managers review reservations for more than one month, they might want to see subtotals by month. You can create a group on month by clicking the DateValue sorting specification, clicking More to expand the options, clicking the arrow on the group on property box (where it says By Entire Value), and clicking By Month. See the sidebar “Understanding Grouping Options” on page 818 for details about other options you can set. Also click the arrow on the group footer property (where it says Without A Footer Section), and click With A Footer Section to create a space to place monthly totals on your report. (Notice that Access changes this specification from Sort By to Group On.) Click the arrow on the option that says Do Not Keep Group Together On One Page, and click Keep Header And First Record Together On One Page as you did for the FacilityName grouping specification.

You can include the DateValue field in the Group, Sort, And Total pane again, but set the group interval to Each Value to create a subtotal by day. Click the Add A Group button to create a blank specification row for a second DateValue. Click DateValue in the Select Field box, click More to see all the options, click the arrow on the group interval box (where it says By Quarter), and then click By Entire Value. Next, click Without A Header Section in the header section box, and click With A Footer Section in the footer section box. Finally, click Keep Whole Group Together On One Page for the last option so that a set of rows for a particular day doesn’t split across a page boundary. You need to move this new grouping specification up one level in the grouping and sorting order, so click the Move Up arrow to move this second DateValue group specification above the RoomNumber sort specification. Remember that there’s no sorting specification in the query you built or in the sample qryXmplRptReservationsByDay query. There wouldn’t be any point in defining a sort in the query because reports ignore any sorting specification from the query when you define any criteria in the Group, Sort, And Total pane. Your result should look something like that shown in Figure 16–4. (Note that we clicked the first DateValue grouping specification so that you can see the group property settings for that field.)

Figure 16–4: Set your grouping and sorting criteria for the Facility Occupancy By Date report in the Group, Sort, And Total pane.

Your report design should now look like Figure 16–5.

Figure 16–5: The Facility Occupancy By Date report has new footer sections after you define the grouping and sorting criteria.

Click the Save button again to preserve your work to this point. You can find this stage of the report design saved as rptXmplFacilityDateOccupancyStep1 in the sample database.

Understanding Grouping Options

For each field or expression in the upper part of the Group, Sort, And Total pane, you can set group on and group interval properties. Normally, you’ll want to start a new grouping of data whenever the value of your field or expression changes. You can, however, specify that a new grouping starts whenever a field or an expression changes from one range of values to another. The type of range you can specify varies depending on the data type of the field or the expression.

For text grouping fields, you can tell Access to start a new group based on a change in value of one or more leading characters in the string. For example, you can create a new group based on a change in the first letter of the field (rather than on a change anywhere in the field) to create one group per letter of the alphabet-a group of items beginning with A, a group of items beginning with B, and so on. To group on such a prefix, use the Custom interval, and enter in the Characters box the number of leading characters that differentiates each group.

For numbers, you can enter a setting for the group interval property that clusters multiple values within a range. In the interval list, you can choose from By 5s, By 10s, By 100s, or By 1000s. Access calculates ranges from 0. For example, if you specify By 10s as the interval value, values ranging from −20 to 29 would be grouped from −20 through −11, −10 through −1, 0. through 9, 10 through 19, 20 through 29. You can also specify a Custom interval value.

For date/time fields, you can set the group interval property to calendar or time subdivisions and multiples of those subdivisions, such as By Year, By Quarter, By Month, By Week, or By Day. Use the Custom setting for the group interval property if you want to group on Hours or Minutes or a multiple of the subdivision-for example, select Custom in the group interval property, enter 2 in the By box, and select Years from the interval combo box if you want groupings for every two years.

When you create groupings in which the group interval property is set to something other than Each Value, Access sorts only the grouping value, not the individual values within each group. If you want Access to sort the detail items within the group, you must include a separate sort specification for those items. For example, if you group on the first letter of a LastName field and also want the names within each group sorted, you must select LastName in the field box in the Group, Sort, And Total pane, select With A Header Section (and possibly With A Footer Section), set the sort order to With A On Top, and set the group interval to By First Character. You must then enter LastName again as an additional sorting specification, set the sort order to With A On Top, and set the group interval to By Entire Value.