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 ...
Get Access 2010: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.