Databases - MS Access Charts

Simple - I created a table of gas prices and the associated dates.
Then I wanted to plot the data.

Wizards

Though MS Access does not provide a charting (graphing) component, there are 2 methods to start a wizard.

From the menu, select Insert / Chart...
Create a new Form or Report and select Chart Wizard

Fill in what ever seems to make sense. (It really doesn't matter, it probably won't work.)

Well, I did all this, and selected an xy plot. The stupid wizard created a plot where the data HAD to be summed or averaged or some other such nonsense.

"All Data fields must be summarized [...] when a date field is used in the Axis or Series."

Well, I tried plots which did not contain dates and got the same crap. It really doesn't matter, once the object is created you can modify the query to get rid of it.

When I tried to view the graph, all I got was a bunch of dots in a vertical column. (Hey, that's pretty worthless.)

Well, of course, there is no help at all.

The chart component is implemented via Microsoft Graph which appears to have all the features of MS Excel graphing. There is even a fair amount of help. However, the help does not say anything about how to get data from MS Access. Instead, there is information on how to provide information from a "datasheet" (a grid which sort of looks like a spread sheet). The problem is that I don't want to type data into the datasheet, I want it to come from a database (like, duh).

After several hours of experimenting, I discovered that the problem was that the application defaults to expect each row of the datasheet to be a series. However, MS Access delivers each series in a column. Simply changing the graph to expect this fixed the main problem.

Once this was done, the query attached to the Row Source property (back in MS Access) was modified to provide the data I wanted to plot.

Microsoft Graph

I have a system where the wizards won't run (not really sure why, don't really care). Another way to graph data is to select

Insert / Object... / Microsoft Graph 97 Chart

and fill in the properties. Be sure to select the chart type and set Data / Series in Columns while still in Microsoft Graph.

Back in MS Access, set the Row Source Type and Row Source.

Actually, this was much easier than using the wizard.

Hint: Be sure not to include the table's auto index field in your plot ... unless you really want it.

Queries

The queries attached to the Row Source property should be standard select statements which return 2 or more values. Normally, the first value is used to label the x-axis - in an xy plot, the first value is the x-value.

SELECT field1, field2, field3
  FROM table
 ORDER BY field1;

Producing an xy Plot

To produce an xy plot,

Create a chart control.
Generate a query for the Row Source property which returns 2 values. The first will be the x values; the second will be the y's.
Double click the chart control to start Microsoft Graph.
From the menu, select Data / Series in Columns.
From the menu, select Chart / Chart Type... / XY (Scatter).
Close Microsoft Graph.
Display the form (or report).

Short Comings

There is no way for the user to interact with the graph. Specifically,

No way to zoom the data once it is displayed.
Moving the mouse over the graph has no effect. Can not identify specific points. (Necessary to determine which record is producing a specific point.)
Microsoft Graph 2000 can plot only 4,000 points per series (verified this using MS Access 2000)

Author: Robert Clemenzi - clemenzi@cpcug.org