Chapter 5: Using PivotTables and PivotCharts to Interact with Data

Overview

PivotTables and PivotCharts are views (usually of a form) that let you interactively select fields for sorting, grouping, and analyzing data in Access tables. A limited version of PivotTables was available in Access 2000, but Access 2002 (Office XP) greatly enhanced PivotTables and introduced PivotCharts. I assume that you have Office XP or higher in this chapter.

If you have Office 2000, see Chapter 4, Sorting and Filtering Data with Queries, for information on crosstab queries, an older (nondynamic) method of analyzing Access data.

When creating a totals query or a crosstab query to analyze your data, you select the fields and the aggregate functions or calculated expressions for use in the query. These choices are static (only the data changes). To analyze sales by day, week, month, and year, you have to create four separate queries. To swap row and column headings, you have to create two queries. PivotTables and PivotCharts give you much greater flexibility. With an appropriate data source query, you can swap rows and columns, sort and group by various fields and expressions, and even filter the PivotTable or PivotChart on the fly—without modifying its design.

In this chapter, I use the term PivotObject to refer to either a PivotTable or PivotChart.

Unlike other Access objects (such as forms and reports), where a great deal of programming effort may be required to prepare a form or report that is easy for the end user to use, PivotTables are easy and intuitive to use and don’t require any programming to set up (except perhaps if you are going to put one on the Web, which is beyond the scope of this book). Therefore, you won’t see any code in this chapter—just information on how to create a suitable data source query, and how to create and modify PivotObjects in PivotTable or PivotChart view.