Slicing and Dicing Data with PivotTables

PivotTables closely resemble Access crosstab query datasheets, which are one of the main topics of Chapter 11, "Creating Multitable and Crosstab Queries." Both PivotTables and crosstab queries employ aggregate functions sum, average, count, standard deviation, variance, and the like to summarize data, but PivotTables can handle the entire aggregation process. This enables PivotTables to selectively display the detail data behind subtotals and grand totals.

Crosstab queries are limited to creating row-by-row subtotals, with optional row (but not column) totals. PivotTables not only provide subtotals but also supply grand totals for rows and columns, plus crossfoot totals. Crossfooting is an accounting term for testing the accuracy of a set of numerical values by comparing grand totals calculated by row and by column. One of the primary advantages of PivotTables over crosstab datasheets is that the user, not the database developer who designed the query, can control data presentation.

PivotTables let you swap axes and apply filters to the underlying data. Like Jet filters for tables and queries, you can use PivotTable filters to remove extraneous or unneeded data from the current view.

Tip

Substitute PivotTables for crosstab queries when your data presentation needs crossfooting or you want to apply sophisticated report formatting to the presentation. It's usually much faster to use PivotTable features to generate row totals, subtotals, and grand totals than it is to use crosstab queries. Another advantage of PivotTables is that users can set the amount of detail information that appears in the report and then generate their own graphs or charts from the data.