Access 2007: The Missing Manual

Chapter 9. Analyzing Data with Crosstab Queries and Pivot Tables

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.

You’ve already learned one way to analyze large volumes of information: with a totals query (Section 7.3). Using a totals query, you can take a huge swatch of rows and reduce it to a few neatly grouped subtotals. In this chapter, you’ll learn about two more specialized options for crunching the numbers: crosstab queries and pivot tables.

Crosstab queries and pivot tables play the same role as the totals queries that you’ve already mastered. However, they present the data in a slightly different way. Crosstab queries use extra columns to pack summary information into an extremely tight table. Pivot tables use a drag-and-drop interface that lets you rearrange your summary on the fly to search for different trends and relationships. Both these features get plenty of usage in the toolkit of every Access expert.

Note

To try out crosstab queries and pivot tables, you need data—and lots of it. The sample databases used in earlier chapters just don’t have enough raw data. Instead, the examples in this chapter use some of the tables from Microsoft’s huge AdventureWorks sample database, which has the product catalog and sales data ...