Section 11.6. Grouping | Access 2007[c] The Missing Manual

11.6. Grouping

Grouping's an indispensable tool for making sense of large volumes of data by arranging them into smaller groups. You can then perform calculations on each individual group. Consider a list of orders in the Boutique Fudge company. Depending on how you want to group your data, you can consider whether chocolate milk outsells chocolate beer, whether customers in New York crave more cocoa than those in Alabama, and so on.

You have three ways to use grouping to analyze information in a report:

Use grouping with a query . In this case, your report doesn't include any details. Instead, it features calculated sums, averages, maximums, or minimums. You don't need any fancy reporting mojo to pull this offjust create a totals query with grouping (as described in Section 7.3), and then use that query to build a report.

FREQUENTLY ASKED QUESTION
Expression Errors

Why does my expression display #Error when I preview it?

The #Error code indicates, unsurprisingly, that something's not quite right with your expression. Access tries to evaluate it, runs into trouble, and shows the error message instead.

You can often get a good indication of the problem by switching back to Design view, and then looking at the offending text box control. Usually, you'll see a green triangle in the control's top-left corner to highlight the problem. Select it, and an error icon appears immediately to the left. You can hover over the error icon to see a description of the problem, and click it to see a short menu of possible fixes that you can apply and error checking options that can tell Access to ignore this problem in the future.

Access error messages are notoriously unclear, so even when you find the error icon and get the details, you may still be in the dark about the real problem. To get you on the right track, consider this short list of common problems:

You didn't start your expression with an equal sign.
You misspelled the field name , or you referred to a field that isn't in the underlying table or query.
You included a mismatched set of parentheses.
Your text box has the same name as one of the fields you're trying to use. If you have the expression =UnitsInStock+UnitsOnOrder and your text box control is named UnitsInStock, Access becomes confused . To resolve this, rename the text box by changing the Name property of the text box to something else (like UnitsInStockCalculation) using the Property Sheet. (The Name property appears at the top of the All tab.)

Use report grouping . This way, you can organize large volumes of information into subgroups. You still see all the details, but you can use subtotals and other calculations. You can also add multiple grouping levels to pull out buried trends.
Use subreports . This way has the same effect as report grouping. The only difference is that you create your report in two distinct pieces.

Note: Subreports are largely a holdover from earlier versions of Access. In Access 2007, the grouping features have improved so much that you no longer need subreports. In this book, you'll focus on getting the most from report grouping, and bypass subreports.

11.6.1. Report Grouping

To create groups, follow these steps:

Switch to Layout view or Design view .
Choose the field you want to use for sorting .

Usually, you should sort your table using the same field you plan to use for grouping.

If you want to group by ProductCategoryID (which creates a separate group of products for each category), then you should begin by sorting your results by ProductCategoryID. That way, all the products in the same group are listed together. (You could also sort by a unique field in the linked ProductCategories table, like CategoryName. Since each product category has a different name, this sorts your products into groups just as effectively.)

Note: If you've created a lookup, Access is smart enough to do the right thing, and use the descriptive field, rather than the linked field, for sorting. ProductCategoryID uses a lookup that displays the matching product name, rather than the underling category ID number that no one really cares about. When you right-click the ProductCategoryID field and choose Sort On, Access actually uses the CategoryName field.
To apply a sort, right-click the field you want to use for sorting, and then choose a sort command (like "Sort A to Z" or "Sort Smallest to Largest") .

The exact wording of the menu command depends on the data type of the field.
Right-click the field you want to use for grouping, and then select Group On. Access sorts your results by that field, and then groups them .

Figures 11-18 and 11-19 show two reports that group products by category.

Figure 11-18. Here, a simple tabular report's sorted and grouped by category with a few quick clicks.

Tip: When you use grouping, it may not make sense to keep your column headers in the page layout section. That's because every group header interrupts your table. Often, you're better off placing the column headers at the bottom of the group header, so they appear at the beginning of every group (not at the top of every page). Figure 11-18 uses this approach. (Figure 11-19 doesn't need to, because it doesn't use any headers at all.) Unfortunately, in order to use this more attractive arrangement, you need to remove your fields from their automatic layout (Section 11.1.3).

Figure 11-19. Grouping works equally well with reports that have complex, non-tabularlayouts. However, you could have a slightly harder time seeing where the groups begin and end, so consider giving the category section a different background color (using the Back Color property described in Section 11.4.1) to make it stand out, as in this example. Or, you can use the line control to create a divider at the top of each category. Figure 11-20 shows this report in Design view.

Grouping works by adding more sections to your report. If you group using the ProductCategoryID field, then your report gains a new section named Product-CategoryID Header, which Access places just above the detail section (see Figure 11-20). This group header includes information about the groupingin this case, the product category. The detail section has the data for each record that's placed in the group.

Note: As you'll see later, you can actually add multiple levels of grouping. When you do, Access adds one group header for each level.

11.6.2. Fine-Tuning with the "Group, Sort, and Total" Pane

Once you have your grouping in place, you have many more options:

You can add an extra layer of sorting that sorts each subgroup .
You can perform summary calculations for each group.
You can force page breaks to occur at the start of each new group.

Figure 11-20. Design view provides the easiest way to add content to the header section of each group. In the ProductCatalog report, you may want to add additional fields from the ProductCategories table to the ProductCategoryID Header section (like the Description).

You can most easily add any of these features with the "Group, Sort, and Total" pane. To show it in Design view, choose Report Design Tools Design Grouping & Totals Group & Sort. Or, in Layout view, choose Report Layout Tools Formatting Grouping & Totals Group & Sort.

The Group, Sort, and Total pane appears at the bottom of the window. Figure 11-21 shows what you'll see if you examine the products-by-category report from Figure 11-19.

Figure 11-21. This report has one level of sorting ( alphabetically by CategoryName) and one level of grouping(byProductCategoryID). To see more options for any given level, select it and then click More (circled). Figure 11-22 shows the grouping settings you can change.

The following sections describe your options in the Group, Sort, and Total pane:

11.6.2.1. Sort by

Chooses the field that's used for sorting. In Figure 11-21, fields are sorted by CategoryName, and then grouped by ProductCategoryID.

Figure 11-22. The Group, Sort, and Total pane gives you a quick way to set up subtotals, headers and footers, and page break options for each level of grouping you use.

11.6.2.2. Group on

Chooses the field that's used for grouping. This option lets you switch up your grouping in a flash.

11.6.2.3. From A to Z/from smallest to largest

Changes the sort order. The exact wording depends on the data type, but you can sort alphabetically for text, numerically for numbers , or chronologically for dates.

11.6.2.4. By entire value

Tells Access to create a separate group for every different value in the grouped field. If you're grouping by ProductCategoryID, then this option ensures that Access places each category in a distinct group. In some situations, this approach creates too many groups, making it difficult to perform any analysis (and wasting reams of paper). In cases like that, you need a way to create larger groups that include more records. If you're grouping products by price or orders by date, then you may prefer to group a range of values, as shown in Figure 11-23.

11.6.2.5. With totaled

Subtotals is the most popular grouping feature. Subtotals lets you compare how different groups stack up to one another. The "Group, Sort, and Total" pane lets you perform calculations using any numeric fields for your subtotals (Figure 11-24).

Depending on what you're trying to accomplish, you can count values, add them, calculate averages, or determine maximum and minimum values in a group. You can place this information into a header that appears at the beginning of each group, or a footer that follows at the end. Finally, you can top your report off with a final grand total that adds up all the subgroups.

Figure 11-23. With date or numeric fields, you can choose to create a group that encompasses an entire range of values. In this example, groups are created by price in intervals of five. The first group has products priced less than $5, the next group stretches from $5 to $9.99, and so on.

11.6.2.6. With title

Click this section to add a fixed title that appears in the category header, at the beginning of each category section. Of course, you can add a title on your own by inserting a label in Design view, but this option provides a convenient shortcut.

11.6.2.7. With a header section / with a footer section

You can apply a header at the beginning of each group, and a footer at the end. Once you add these sections, you can place any content you want in them using Design view. You'll use them most often to display information about the entire group, show subtotals, or draw separating lines with the line control (Section 11.1.4).

11.6.2.8. Keep group together on one page

This setting helps you prevent orphaned category headers. In the product catalog example, this option makes sure you don't wind up with a group title like Beverages at the bottom of a page, and all the matching products on the following page.

Ordinarily, Access doesn't prevent awkward page breaks. Instead, it simply tries to fill each page. If this isn't what you want, then you have two other choices. You can choose to make sure the entire group's always placed on the same page ( assuming it's less than one page long), or you can choose to make sure the header and at least one record are kept together on the same page.

Figure 11-24. In this example, the list of products is grouped by category, and the average price of each category is displayed in the footer (circled).

One option the "Group, Sort, and Total" pane doesn't offer you is forcing a new page break at the beginning of each group. To accomplish this, you need to switch to Design view, select the group header section, and then, in the Format section Property Sheet, look for the Force New Page setting (Section 11.5). Set it to Before Section to force a page break at the beginning of each new section, or After Section to force the page break at the end of the section. (You don't see a difference between these two settings unless you're using a report header and footer. If you have a report header and you use Before Section, then you end up with a page break between the report header and the first section.)

Note: You don't see Force New Page setting's effect in Layout view, Report view, or Design view. It appears only when you use the Print Preview feature (Section 10.2), or when you actually print your report.

In many scenarios, you'll want a group to start on a new page. This stipulation makes sense when printing the class list shown in Figure 11-25. In this case, the Force New Page setting lets you avoid putting two attendance lists on the same page.

Figure 11-25. This class attendance list uses several of the tricks you've learned in this chapter to create a printout that's a world away from a typical report. The report displays a list of students grouped by class. There's no report header, but the group header for each class adds a few key pieces of information from the Classes table. Each class group starts on a new page thanks to the Force New Page settings, and each student's followed by a row of hand-drawn rectangle controls where you can tick off the attendance.

11.6.3. Multiple Groups

Your reports aren't limited to a single group. In fact, you can add as many levels of sorting and grouping as you want, to slice and dice your data into smaller, more tightly focused subgroups.

To add another level of grouping, just right-click the field you want to use, and then click Group On. This adds it to the list in the "Group, Sort, and Total" pane. (You can also add additional levels of sorting by right-clicking a field, and then choosing a sort command. If you began by sorting and grouping your products into categories, then you could sort each category by product name.)

When you have more than one group in the "Group, Sort, and Total" pane, it's important to make sure they're applied in the right order. If you try to group a list of ordered items by customer and then by product category, you'll get a different result from the one you get if you group first by category and then by customer. Figure 11-26 illustrates the issue.

Each group you add can have a header and footer section and its own set of totals. You add these ingredients using the "Group, Sort, and Total" panejust select the appropriate grouping level, click More, and then change the various options, as described in the previous section.

Figure 11-26. Access applies groups in a top-to-bottom order. So in this example, the results are grouped by CustomerID, and then by OrderID. If you don't want this outcome, then select one of the grouping levels, and then click the up or down arrow button to move it. (To remove a grouping level altogether, select it and then press Delete.)

Figure 11-27. This report transforms order information into a printable invoice using grouping and a few other tricks you've seen already. The raw information comes from the OrderDetails table (supplemented with information from the Orders, Product, and Customer tables). Line controls separate the different sections of the invoice, automatically generated totals tell you how much the order costs altogether, and expressions combine a few fields and add some extra text (like "Order History for" and "Order #"). This example's included in the Boutique Fudge database with the downloadable content for this chapter.

When you have more than one level of grouping, you can hide some of your information so you see just the totals. In Layout view, just choose Report Layout Tools Formatting Grouping & Totals Hide Details. If you use this technique on the example in Figure 11-27, Access hides the line-by-line order details, and all youll see is the total for each order.

Tip: The invoice example creates a report that prints invoices for all the orders in your database. However, you can use filtering (Section 3.2.2) to filter down the results to a specific order or customer.