Access 2010: The Missing Manual

Summarizing Data

All the queries you’ve used so far work with individual records. If you select 143 records from an Orders table, you see 143 records in your results. You can also group your records to arrive at totals and subtotals. That way, you can review large quantities of information much more easily, and make grand, sweeping conclusions.

Some examples of useful summarizing queries include:

• Counting all the students in each class.

• Counting the number of orders placed by each customer.

• Totaling the amount of money spent on a single product.

• Totaling the amount of money a customer owes or has paid.

• Calculating the average order placed by each customer.

• Finding the highest- or lowest-priced order that a customer has placed.

These operations—counting, summing, averaging, and finding the maximum and minimum value—are the basic options in a totals query. A totals query is a different sort of query that’s designed to chew through a large number of records and spit out neat totals.

To create a totals query, follow these steps:

1. Create a new query by choosing Create→Queries→Query Design.

2. Add the tables you want to use from the Show Table dialog box, and then click Close.

   The following example uses the Products table from the Boutique Fudge database.

3. Add the fields you want to use.

   This example uses the Price field, but with a twist: The Price field is added three separate times. That’s because the query will show the result of three different calculations.

4. Choose Query Tools | Design→Show/Hide→Totals ...