Integrating Excel and Access

Chapter 6. Using Excel Charts and Pivot Tables with Access Data

There is something about putting your information into a colorful chart or summarizing a tremendous amount of data in a pivot table that really adds to an analysis. Unfortunately, some Excel users struggle with putting together a simple chart, and many have never even heard of pivot tables. This chapter has two main goals: first, to explain why these built-in Excel features are important and how they can be used; and second, to show how to automate reporting of Access data through Excel charts and pivot tables with VBA.

Excel has many built-in standard chart types, as well as some custom chart types. There are several custom chart types that allow you to plot data on two axes, which is very useful for analytical functions. For example, you can graph sales on one axis and margin on another to see if periods of high sales correspond with lower margins. While you could see the same thing by looking at a numbers-only report, using a chart really illustrates the analysis. These are the standard types of charts that are built into Excel 2003:

Column	Line	XY (Scatter)
Donut	Surface	Stock
Cone	Bar	Pie
Area	Radar	Bubble
Cylinder	Pyramid

Excel also offers a number of built-in custom charts:

Area Blocks	B&W Column	B&W Pie
Colored Lines	Columns with Depth	Floating Bars
Line-Column on 2 Axes	Logarithmic	Pie Explosion
Stack of Colors	B&W Area	B&W Line (Timescale)
Blue Pie	Column-Area	Cones
Line-Column ...