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 everythinginstead 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 dataand 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 for a fictional bicycle manufacturer. Surf over to the "Missing CD" page for this book (at www.missingmanuals.com) to download everything you need.