Chapter14.Pivot Tables and Pivot Charts

Chapter 14. Pivot Tables and Pivot Charts

In Chapter 9, "Queries, Part II," you learned about crosstab queries; in this chapter, you learn about pivot tables. Both summarize data in two-dimensional matrixes. But pivot tables are much more powerful than crosstab queries: They add extra levels of detail for more in-depth analysis. They are extremely flexible, so you can "slice and dice" your data in many ways. They make it easy to find all kinds of totals, such as sums, counts, and averages, for many sets and subsets of data.

And pivot tables are fun. Yes, they do entail a learning curve. There's a lot going on in a pivot table, and you can set many properties that affect both substance and appearance. But when you know how pivot tables work, you'll find yourself pivoting like crazy. Pivot tables are useful for managers who want to view and analyze data from many standpoints. You can quickly move from finding the average monthly revenue of the Wilmington office for the third quarter to finding the total number of orders for the Dayton branch last year. Extended analysis can be a substitute for action, but more frequently, the powerful analytical tools that pivot tables offer will help you make better decisions.

The term pivot table itself is a little deceptive. True, pivot is an apt adjective for the kind of data gymnastics it enables you to perform. And it is a table in the broadest sense of the word: The end product is a grid with a lot of values and sums, as you might expect from a feature that has its origins in Excel. (Until the most recent Access versions, pivot table creation in Access was executed through an embedded Excel object.)

But a pivot table is not another type of Access table (the term I'll use for the tables you've been working with all along). When you create a table, there is no pivot table selection in the New Table dialog box. A pivot table is not a full-fledged object, but rather a view, as in Datasheet view. Although it enables you to examine data in meaningful ways, you cannot add or edit records in PivotTable view. (I use the Access spelling of PivotTable when appropriate, but otherwise I prefer the simpler pivot table.)

Furthermore, you'll usually build a pivot table based on a query, not a table. That makes sense if you think of a pivot table as a crosstab query's big brother. In both, you usually combine data from several tables so you can break down and sum the data for analysis. It's unlikely that all the data you need will be in a single table because tables are organized by subject, not mission objective.

As with a pivot table, a pivot chart is a dynamic view of data that can be quickly changed to examine different data from different standpoints. As a chart, of course, the perspective is graphical instead of textual. Pivot tables and pivot charts are close cousins, but not necessarily kissing ones. Design and data changes in one are reflected in the other. But if you want to build a pivot chart, you just go ahead and do ityou don't need, or even necessarily want, to build a pivot table first.

NOTE

In earlier versions of Access, the Pivot Table Wizard was central to creating pivot tables. With the introduction of views, however, the wizard has lost much of its value, although it remains a selection in the New Form dialog box. Similarly, you can continue to use Microsoft Graph to create charts in Access, but I think you'll prefer pivot charts when you learn how to build them.