PivotTable and PivotChart Views

Access 2000 introduced the Office Web Components (OWC), which include PivotTable, PivotChart, and Spreadsheet ActiveX controls, for use with Data Access Pages. Access 2002 added PivotTable and PivotChart views of tables, queries, and forms in traditional Access applications, ADP, and DAP. In most cases, PivotTable and PivotChart views use summary or crosstab queries as their data source. Data in conventional relational tables seldom is suited to display in either of these new views. You can specify the default view of a table, query, view, or function to be a PivotTable or PivotChart.

PivotTables let you manipulate a spreadsheet-like view of data by increasing or decreasing the level of detail and filtering the data to reduce the number of rows, columns, or both. You also can rotate (pivot) the view by interchanging rows and columns. The PivotTable's AutoCalc feature lets you generate the equivalent of a Jet crosstab query from detail or summary data and add grand totals to rows and columns. Transact-SQL (T-SQL), SQL Server's query language, doesn't have direct equivalents to the Jet SQL TRANSFORM and PIVOT statements that generate crosstab queries. You can use PivotTables to emulate Jet crosstab queries in ADP and DAP that use SQL Server as the data source.

To learn how to take advantage of PivotTables, see "Slicing and Dicing Data with PivotTables," p. 463.

By default, PivotTable and PivotChart views of queries you select from the View button's drop-down menu are empty when you open them. You start by selecting the PivotTable view and opening the Field List dialog of the query. Then, drag fields from the Field List to one of four drop zones: Row Fields, Column Fields, Totals or Detail Fields, and Filter Fields. Figure 1.11 illustrates the PivotTable view of the Product Sales for 1997 query of the Northwind sample database. The Dairy Products category is expanded to display sales subtotals for individual products.

Figure 1.11. This PivotTable view of a summary displays total order amounts for product categories, with subtotals for individual products of the Dairy Products categories. In addition to generating subtotals, the AutoCalc feature adds grand totals for rows and columns.

PivotCharts rely on PivotTables to calculate values that display in several chart formats. PivotCharts reflect changes you make to the PivotTable layout and vice-versa. The PivotChart shown in Figure 1.12 corresponds to the PivotTable of Figure 1.11 with the Dairy Products category collapsed. The chart's legend is an extension to the ShippedQuarter filter button.

Figure 1.12. Collapsing the Dairy Products category in PivotTable view generates a PivotChart with eight groups of four bars representing quarterly sales of each product category.

Interaction between the PivotTable and PivotChart views can confuse users who aren't accustomed to manipulating PivotTables. Using a form or subform bound to a query to display a PivotTable or PivotChart is a better approach than delivering these views of a query directly. You can use form properties to control which views are accessible to the user and add VBA code to limit the extent to which the user can modify the PivotTable. The Sales Analysis form of the NorthwindCS ADP is an example of a simple application that uses a command button to alternate between PivotTable and PivotChart views of a query.

To learn how to design PivotCharts, see "Formatting and Manipulating PivotCharts," p. 481 and "Working with PivotChart Forms," p. 726.