Section 7.3. Summarizing Data | Access 2007[c] The Missing Manual

7.3. 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 operationscounting, summing, averaging, and finding the maximum and minimum valueare the basic options in a totals query . A totals query's 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:

Create a new query by choosing Create Other Query Design .
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.
Choose Query Tools Design Show/Hide Totals .

Access adds a Total box for each field, just under the Table box.
For each field, choose an option from the Total box. This option determines whether the field is used in a calculation or used for grouping .

A totals query is slightly different from a garden-variety query. Every field must fall into one of these categories:
- It's used in a summary calculation (like averaging, counting, and so on) . You pick the type of calculation you want to perform using the Total box. Table 7-7 describes all the options in the Total box.
- It's used for grouping . Ordinarily, a totals query lumps everything together in one grand total. But you can subdivide the results into smaller subtotals, as described in the next section.
- It's used for filtering . In this case, in the Total box, you need to choose WHERE. (Database nerds may remember that Where is the keyword used to define criteria in SQL, as described in Section 6.2.3.1.) You also need to clear the checkmark in the Show box, because Access doesn't have a way to show individual values in a totals summary.

Note: If you try to add a field to a totals query that isn't used for a calculation, isn't used for grouping, and isn't hidden, you'll receive an error when you try to run the query.

In this example (Figure 7-8), the Price field uses three different summarizing options: Max, Min, and Avg.

Figure 7-8. Top: This totals query includes the same fieldPricethrice, and uses three difficult calculations. Notice that each field uses an expression that provides a more descriptive name (Section 7.1.2).
Bottom: The results show a single record with the maximum price, minimum price, and average price of the products sold by Boutique Fudge.

Note: Table 7-7 leaves out two options that are tailor-made for statisticiansStDev and Varwhich calculate the standard deviation and variance of a set of numbers .

Table 7-7. Options for Summarizing Data

Choice in the Total Box	Description
Group By	Subgroups records based on the values in this field
Sum	Adds together the values in this field
Avg	Averages the values in this field
Min	Retains the smallest value in this field
Max	Retains the largest value in this field
Count	Counts the number of records (no matter which field you use)
First	Retains the first value in this field
Last	Retains the last value in this field

You can use all the same query-writing skills you picked up earlier in this chapter when designing a totals query. If you want to summarize only the products in a specific category, you can use a filter expression like this in the CategoryID field:

=3

This expression matches records that have a CategoryID of 3 (which means they're in the Candies category).

Note: If you want to perform a filter on a field that you aren't using for a calculation or grouping, make sure that in the Total box, you choose Where, and in the Show box, you clear the checkmark.

7.3.1. Grouping a Totals Query

The simplest possible totals query adds all the records you select into a single row of results, as shown in Figure 7-8. A more advanced totals query uses grouping to calculate subtotals .

The trick to using grouping properly is remembering that the field you use should have many duplicate values. For example, it's a good idea to group customers based on the state in which they live. Because a given state has many customers, you'll end up with meaningful subtotals. However, it's a bad idea to group them based on their Social Security numbers, because you'll end up with just as many groups as you have customers. Figure 7-9 shows an example of totals query that uses grouping.

You can use multiple levels of grouping in a totals query by adding more than one field with the Total box set to Group By. However, the results might not be exactly what you expect. Suppose you group a long list of sales records by product and by customer. You'll end up with a separate group for every customer-and-product combination. Here's part of the results for a query like this that groups records from the OrderDetails table in the Boutique Fudge database and then sorts them by CustomerID:

Figure 7-9. Top: Here, products are grouped by product category.
Bottom: The result: a separate row with the totals for each product category.

Table 7-8.

CustomerID	ProductID	TotalSales
10	108	$432.12
10	134	$16.79
10	210	$53.30
14	144	$18.99
18	112	$107.04
18	210	$12.02

This table tells you that customer #10 has spent a total of $432.12 dollars on product #108 across all orders. Customer #10 also spent a total of $16.79 on product #134, $53.30 on product #210, and so on. (You could take the same information and sort it by ProductID to look at the total sales of each product to different customers. You still get the same information, but you can analyze it in a different way.)

This is the result you wantsort of. It lacks nice subtotals. It would be nice to know how much customer #10 spent on each type of product, and how much customer #10 spent in total. But thanks to the rigid tabular structure of the totals query, this result just isn't possible.

If you want to look at this subgrouped information with subtotals, you have two choices. You can use a crosstab query or a pivot-table querytwo advanced summary options that are described in Chapter 9. Or, if you're really interested in printing out your information, you can generate a report that includes multiple levels of grouping and subtotals, as described in Part Three.

7.3.2. Joins in a Totals Query

Summary queries are insanely useful when you combine them with table joins (Section 6.3) to get related information out of more than one table. In the Boutique Fudge database, the OrderDetails table stores the individual items in each order. You can group this information (as shown in the previous section) to find top-selling products or customers. However, you see only the customer and product ID values, which isn't very helpful.

Note: If you have a lookup defined on the ProductID field and CustomerID field, you will see the descriptive information from the lookup (like the product name or customer name). This information helps a bit, but you may still want to pull extra informationlike the customer's address, the product description, and so onout of the linked table.

If you throw a join or two into the mix, you can pull in related information from linked tables (like Customers, Products, and Orders) and add it to your results. Figure 7-10 shows an example that groups the OrderDetails table by OrderID to find the total cost of each order. It then sorts the results by CustomerID.

You already know enough to build the query shown in Figure 7-10. Here's what you need to do:

Create a new query by choosing Create Other Query Design .

Choose Query Tools Design Show/Hide Totals .

This adds the Total box for each field.

Figure 7-10. Top: This totals query gets more advanced by drawing from three related tablesCustomers, Orders, and OrderDetailsto show a list of order totals, organized by customer. The query ignores orders less than $25. You could also add a filter expression on the DatePlaced field to find out how much customers spent so far this year, how much they spent last year, how much they spent last week, and so on.
Bottom: The results are grouped by OrderID and sorted by LastName and FirstName, which preserves a good level of detail.

Add the fields you want to use, and then, in the Total box, choose the appropriate grouping or summarizing option for each one .

You can choose your fields from any of the linked tables. This example uses several fields:
- OrderID . This field's used to group the results. In other words, you want to total all the records in the OrderDetails table that have the same OrderID. To make this work, in the Total box, choose Group By. (Incidentally, it makes no difference whether you choose the OrderID field in the OrderDetails table or the ID field in the Orders tablethey're both linked.)
- OrderTotal . This field's a calculated field that uses the expression [Price]*[Quantity] to multiply together two fields from the OrderDetails table. The result's the total for that individual line of the order. Access adds up all these line totals to create the grand order total, so set the Total box to Sum. In addition, the OrderTotal field includes the filter expression >=25, which hides any orders that have a combined value of less than $25.
- LastName and FirstName . These fields identify the customer who made the order. However, there's a trick here. In order to show any field in a totals query, you need to perform a calculation on it (as with OrderTotal) or use it for grouping (as with OrderID). That means you must set the Total box to Group By for both LastName and FirstName. However, this setting doesn't actually have an effect, because every order's always placed by a single customer. (In other words, you'll never find a bunch of records in the OrderDetails table that are for the same order but for different customers. It just isn't possible.) The end result is that Access doesn't perform any grouping on the LastName and FirstName fields. Instead, they're simply displayed next to every order.
Note: This grouping trick's a little weird, but it's a common technique in totals queries. Just remember, Access creates the smallest groups it can. If you want to group by customers only (so you can see how much everyone spends), you simply need to remove the OrderID grouping and group on CustomerID instead. Or, if you want to total all the sales of a particular product, remove all the customer information, group on ProductID, and then add any extra fields you want to see from the Products table (like Product-Name and Description).
You can now run your query .