Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

659

FIGURE 18.16

This datasheet was created from a total query against tblProducts.

Performing totals on groups of records

Most of the time, you need to perform totals on a group of records rather than on all records. For

example, you may need to calculate the query for each category of product. In other words, you

want to create a group for each type of product (car, truck, motorcycle, and so on) and then per-

form the total calculations against each group.

Calculating totals for a single group

When you create your query, you specify which field or fields to use for grouping records and

which fields to perform the totals against. Using the preceding example, to group on the Category

field, select the Group By option in the Total cell:

1. Open the qryProductAggregateFunctions query in Design view.


2. Add the Category field to far left side of the QBE grid.


3. Make sure the Total cell for Category is set to Group By.

The query in Figure 18.17 groups all like products together and then performs the calcu-
lations on each type of product. Unlike performing totals against all records, this query
returns one record for each type of product. Figure 18.18 shows how the datasheet looks.

FIGURE 18.17

Totals against records grouped by product category