Access 2010: The Missing Manual

Pivot Tables

If totals queries and crosstab queries just don’t thrill you enough, Access has yet another high-powered feature for summarizing your data. A pivot table is a specialized table that performs the same tricks as a crosstab query—row and column grouping—but has even more muscle. Here are some of the extra features:

• Pivot tables can be rearranged at any time. With a quick drag of the mouse, you can convert a sales-by-country summary to a sales-by-customer-age grid. That makes pivot tables great for data exploration, in which you try to ferret out hidden trends and relationships from an avalanche of raw data.

• Pivot tables support unlimited levels of grouping. You aren’t limited to one level of column grouping, as you are in a crosstab query. Instead, you can subdivide your rows and columns into smaller and smaller groups.

• Pivot tables are collapsible. You can hide row and column groups you aren’t interested in at the moment, and you can dig down into a group to see the individual records it contains. By browsing your data in this way, you can get a better idea of what’s taking place with your data.

• Pivot tables support unlimited calculations. Crosstab queries can perform only a single calculation, which is repeated for each group. A pivot table can perform as many calculations as you want, and it stuffs them all into the same cell.

• Pivot tables support sorting by your calculated values. For example, if your pivot table adds up total sales, you can sort it so the best performers ...