Grouped Reports

Grouped reports display data hierarchically, sometimes indenting data from lower groups (indentation is practical only for reports with a small number of fields). The lowest level may contain data from individual records or summarized data. You can create a grouped report using the Report Wizard, but the results are not likely to be acceptable (see the “Report Wizard” section earlier in this chapter for an example). Therefore, I prefer to create grouped reports directly from Design view, using a copy of the appropriate report template.

As an example of a simple grouped report, I started by making a copy of the letter portrait grouped report template, and selected qryOrders as its record source. I selected ToyCategory and Salesperson as sorting and grouping levels with both headers and footers, and Customer and ToyID for further sorting, without headers and footers, as shown in Figure 6.9.

Figure 6.9

I edited the dummy text and field names in the group header and footer textboxes, replacing “Client Name” with “Toy Workshop” in txtTitle and placing “Shipping” before “Department” in txtSubtitle. I also removed txtDateRange, because it isn’t needed for this report. In the Detail section, I placed a textbox to display the customer name, with Hide Duplicates and CanGrow both set to Yes (to prevent printing the customer name more than once in a group and to let long customer names wrap to the next row). I created a calculated field to concatenate information about the toy purchased, with the control source listed below:

=[Quantity] & “ of Toy ID “ & [ToyID] & “ (“ & [Toy] & “) @ “ & Format([UnitPrice],”Currency”)

and I placed the TotalPrice field on the report, changing its label caption to “Order Price.”

There are several group subtotals on this report. If you look at the textboxes that display these subtotals, you’ll see that they all have the same expression for the ControlSource property:

=Sum([TotalPrice])

This expression yields different values depending on where it is placed in the report. In a group footer, it yields a subtotal for that group, and in the report footer, it yields a grand total for the entire report.

The report is shown in print preview in Figure 6.10.

Figure 6.10

This page of the report shows two orders for one customer under Sheryl Gleason’s group, and two orders for one customer and one order for another customer under Kenneth Gould’s record. If you wanted a more compact report format, with customer subtotals, you could add a group header and footer for the Customer field and eliminate the calculated field, placing Quantity, ToyID, Toy, and UnitPrice textboxes directly on the form. I added more descriptive text to the group footer subtotals, to clearly differentiate the group levels, made them right-aligned, and moved them closer to the totals. I also added descriptive column labels to the Customer group header, to identify the fields. Version 2 of the report is shown in Figure 6.11.

Figure 6.11

One more change might be beneficial: Set the Force New Page property of the ToyCategory group footer to After Section so that each toy category starts on a new page. Figure 6.12 shows the third page of version 3 of the report, with a small category on its own page.

Figure 6.12

If you see blank report pages between pages with text when printing or previewing a report, this is probably because you accidentally moved or resized a control so that it goes beyond the available report width. Curiously, Access doesn’t show guidelines for the report size in Design view, nor does it prevent you from dragging a control too far to the right, so this is a common occurrence. If you drag or resize a control beyond the available space, Access just changes the report width correspondingly—and you get the overflow printing on the next page, on an extra, unnumbered page.

To fix the problem, check that no controls go beyond the available space for the report width, and set the report width back to its original value, which should be the paper width minus the left and right margins.

One final touch that could be added to a grouped report is running sums, and possibly page totals. Access doesn’t support page totals directly. This means that if you place a textbox with a Sum or Count expression in a report’s page footer section, you’ll just see #Error when you preview or print the report.

However, there is a workaround for the lack of support for page totals: Create a one-line event procedure for the Print event of the Detail section, which adds the values of the TotalPrice field and places the sum in an unbound txtPageTotal textbox. This event procedure is:

 Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer) On Error GoTo ErrorHandler        Me![txtPageTotal] = Nz(Me![txtPageTotal]) + Nz(Me![TotalPrice])     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

Running sums are easier: all you need to do is add a textbox bound to the field for which you want a running sum, and set its RunningSum property to either Over Group (to reset the running sum every time a new group starts) or Over All, to keep on adding to the running sum all through the report. Figure 6.13 shows version 4 of the grouped report, with a page total and both group and overall running sums.

Figure 6.13