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 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. - 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 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) 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.
Note: Table-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. 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 tutorial 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.
