Pivot charts were introduced in Access 2002. Amazingly, they are like golden nuggets hiding under a rock; they have not been publicized and exploited. Anyone who is struggling with cross tab queries should take a look at PivotTable and PivotChart views for forms.
These incredibly powerful views allow users to look at data in countless ways. It can be drilled into, grouped by criteria, limited to the top X, expanded, sorted, totaled, subtotaled, charted, diagramed, and the list of criteria is seemingly endless. The number of chart options is absolutely amazing, and developers can let the user interact with the data and even change the chart type on the fly.
Although there are wizards for creating PivotCharts and PivotTables, most people find them rather intuitive, especially after doing a couple of experiments. And, they are so easy to modify after they are setup that you will want the ability to change them. There are several ways to create a PivotChart. In addition to using the wizard or creating a form in Design View, you can open an exiting form and switch to PivotChart view to see what it would look like. Of course, it may be prudent to take the conservative approach of making a copy of a form and using that to experiment, rather than risk unintentionally making changes to a functional form. Alternatively, this would be a good time to employ the nifty new database backup feature.
Once you have created a PivotChart, it is remarkably easy to add filters, replace fields, or expand and collapse the details. As shown in Figure 3-17, with a right-click of the mouse you can even select a different type of chart. Any manager will appreciate the ease of changing both the detail and the display format on the fly.
Was this article helpful?