O'Reilly logo

Access 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 (Summarizing Data). 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 for a fictional ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required