Grouping a Totals Query

The simplest possible totals query adds all the records you select into a single row of results. 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.

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.

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. Or, if you're really interested in printing out your information, you can generate a report that includes multiple levels of grouping and subtotals.