Scatter Graphing–Tutorial? (Access 2002)

Topic

New Reply

Hi All,

My lovely supervisor has provided me with a set of scatter graphs in MS Excel created by someone else. He has requested that I duplicate them with a whole bunch of very labor-intensive manual data updates. Even worse for me because I am TERRIBLE at MS Excel and have been having a hellish time trying to figure it out.

I have managed to get the same data into MS Access (where I can calculate the data with queries and code, instead of doing it manually), but likewise cannot for the life of me figure out how to even begin to do the scatter graphs.

The wizard is just terrible…I want a list of items going across the x axis, but it keeps putting numbers…and not even the numbers that are in the underlying datasheet created by the wizard! Ugggh.

Can someone suggest a tutorial or book or something that can hand-hold me into doing the scatter graphs? Please? I’m in tears trying to figure this out with zero assistance from anyone.

Reply | Quote

Replies

I agree that the Chart Wizard is virtually useless. Fortunately, scatter charts are less difficult than it seems as first. Try the following:

1) Create a query that will act as data source for the chart. The query should have two fields: one will act as X coordinates, the other as Y coordinates.
2) Activate the Forms section of the database window.
3) Click New at the top of the database window.
4) Select Chart Wizard and select the query you created, then click OK.
5) Add both fields, then click Next.
6) Select the XY scatter chart type, then click Next.
7) One field will already be in the box near the Y axis, saying Sum of …. Double click the box and set the summary option to None.
8) Drag the other field to the box near the X axis.
9) Click Finish.
10) Double click the chart.
11) Click ‘By column’ on the toolbar (‘By row’ is the default, but our query has the data by column).

That’s it, basically – you can fine-tune the formatting if needed, but the scatter chart is there.

Reply | Quote

Okay, I think I’ve figured out that what I need to do *isn’t* necessarily scatter graphing, it’s just the fancy name/word of the day that the supervisor wants to call it (dufus). At least that I can’t get names going across the X axis (ie. plotted?), but I can with a line column chart. Ugggh, all that has taken me since 8am this morning!

Your mini tutorial is excellent! Thank you!!! Now I need to go further.

What I’ve got is a list of stores, in regions, with their sales, target sales, and the number of hours they’re open in the month. It looks like this:

Region   Store   Sales   TargetSales  Hours      Cafe
East           A          1100      1200                200        T
East          B           1120      1000                150        F  

You get the picture.

I’m trying to create a graph that compares sales to target sales, and then adds data for the number of hours on there (with a y axis on the right side scaled to the number of hours–just for the heck of it lol). To put a bigger quirk in it, I need separate graphs per region (or at least I should be able to program the data source to change with a drop down box, so I can see the different regions?). And then the original Excel graphs have two parts to them, they have a small graph grouping together stores that have cafes and then a line and then the rest are the stores that don’t have cafes.

Now that I’ve written that all out…ugggh…I’m going back to try to work with your example to be able to change the region. I think if I can get past that…it’s a huge hurdle…I still have to do the other impossible parts, but I will feel better if I can figure out how to program the change in region….

Reply | Quote

I just had a side thought.

Is it at all possible to put the actual Excel chart object (which I assume is an OLE object?) into Access? And then change the datasource of that somehow?

Reply | Quote

You can embed an Excel chart in an Access form or report, but I don’t see advantages to that. On the other hand, you can import Access data into Excel and use them as the basis for a chart in Excel. The data can be refreshed automatically or manually. I use this if I want to create complicated charts that are difficult to design in MS Graph.

Reply | Quote

I think that is ultimately the solution. I thought I had everything set up the way you said, but it just doesn’t work easily, and ultimately, I can’t get the exact charts they want. Okay, my new question: Can you suggest a tutorial on Excel graphs? The ones this person used are CLEARLY not standard, and I can’t figure out how he made them.

Reply | Quote

Excel MVP Jon Peltier has a series of Chart Tutorials. You’ll also find a wealth of other charting-related material on his website.

Reply | Quote

A scatter chart by definition has numeric values along both axes, so if you need text labels along the X axis, you cannot use a scatter chart. A line chart can do what you need.

If you want a chart that changes by region, you can do the following:
– Set the record source of the form (or report) to a table or query that lists the regions.
– Place a text box bound to the region field on the form/report.
– Edit the query that acts as row source of the chart to add a condition to the region field that looks like

[Forms]![NameOfForm]![NameOfTextBox]

or

[Reports]![NameOfReport]![NameOfTextBox]

– Set the Link Master Fields and Link Child Fields properties of the chart control to the region field.

As you move from record to record in the form, the chart should update itself automatically.

Reply | Quote