Access 2013: The Missing Manual

Chapter 8. Queries That Summarize Data

Access is ready and willing to store all the details in your database. But sometimes you don’t need to know everything—instead you just want the big picture. You need a way to take your raw data, which may include hundreds or thousands of records, and summarize it in some meaningful way.

In this chapter, you’ll look at two ways to analyze large volumes of information. First you’ll use totals queries to boil columns of numbers down to neatly grouped subtotals. Next, you’ll learn about crosstab queries, which use extra columns to pack summary information into extremely tight tables. Both features provide the same data-summarizing service, but they present the data in a slightly different way.

Note

If you’ve used previous versions of Access, you might remember that it included two more data analysis tools: pivot tables and pivot charts. Access 2013 drops these features. If you need them, you’ll find more powerful versions of both in Excel 2013. The only catch is that you’ll need to use Excel’s data connection features to let it get to your Access data.

Totals Queries

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 ...