x-y plots

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Big Smelly Ogre - 27 Jan 2006 18:58 GMT

How can I get a simple x-y plot? The wizard insists on grouping and summing
the data, which is not what I need. A search of this forum didn't shed any
light on the subject.

Reply to this Message

LeAnne - 27 Jan 2006 19:11 GMT

Please post the SQL for the query in the RowSource property for the
chart. If there's an underlying query, post that SQL as well.

LeAnne

> How can I get a simple x-y plot? The wizard insists on grouping and summing
> the data, which is not what I need. A search of this forum didn't shed any
> light on the subject.

Reply to this Message

The Big Smelly Ogre - 27 Jan 2006 20:25 GMT

Here is the underlying query:

SELECT Resource, Latitude, Longitude
FROM qryDeposits
ORDER BY Resource;

Here is the query that the wizrd places into the chart's Row Source:

SELECT [Resource],Sum([Latitude]) AS [SumOfLatitude]
FROM [qryGraph]
GROUP BY [Resource];

What I want is a simple plot of latitude vs. longitude, with a different
symbol for each resource. I can export the query to an Excel spreadsheet and
plot it, but I would prefer to do it within Access. Eventually I want to be
able to add controls to enble the control of the plotted range and scale, but
I need to get the basic graph to work first.

> Please post the SQL for the query in the RowSource property for the
> chart. If there's an underlying query, post that SQL as well.
[quoted text clipped - 4 lines]
> > the data, which is not what I need. A search of this forum didn't shed any
> > light on the subject.

Reply to this Message

LeAnne - 27 Jan 2006 20:44 GMT

Access wizards are often quite stupid. You'll find a workaround for the
pointless requirement to sum or otherwise aggregate chart data at

http://www.cpcug.org/user/clemenzi/technical/Databases/MSAccess/Charts.html

hth,

LeAnne

> Here is the underlying query:
>
[quoted text clipped - 22 lines]
>>>the data, which is not what I need. A search of this forum didn't shed any
>>>light on the subject.

Reply to this Message

The Big Smelly Ogre - 30 Jan 2006 14:30 GMT

Thanks for your help. I fully appreciate the time and effort that goes into
helping others in these forums.

That said, I played with this all weekend, and it seems that the best I can
get access to do at producing an x-y plot is really more of a bar chart
without the bars. All of the x data must fall on a given, evenly spaced line.
My data's not so neat.

I need to plot what are essentially coordinates on a map, something like this:

point x y
1 16542.0 8546.4
2 17634.5 6385.4
3 15964.8 7642.3

This is very easy to do in Excel, and I have set it up to plot my data. But,
it would be much more useful to do it from within Access. And, since both
programs use the same graphing system, it should be possible. The question
is, how?

> Access wizards are often quite stupid. You'll find a workaround for the
> pointless requirement to sum or otherwise aggregate chart data at
[quoted text clipped - 31 lines]
> >>>the data, which is not what I need. A search of this forum didn't shed any
> >>>light on the subject.

Reply to this Message

LeAnne - 31 Jan 2006 19:26 GMT

BSO,

It's certainly possible. Did you go to the link I posted? There are
pretty straightforward instructions on how to create an xy scatterplot.
As I said before, the Access ChartWizard is stupid. I would only rely
on the Wizard to throw a chart (any old chart) on my Form quickly. Then
I prefer to modify the RowSource query, change the chart type etc.
manually. Some thoughts:

Are you sure your coordinates are really NUMBERS? Lats and longs (or
UTMs, or other spatial data) aren't "numbers" in the sense that you can
do math with them. After all, 16542.0 + 8546.4 doesn't produce anything
meaningful. Access and MS Graph require at least 1 field to be numeric.

Second (as was pointed out in the link), it's easy to modify the
underlying query manually. Just remove the Sum() or other aggregate
function from the SQL statement. You'll need to also delete the GROUP
BY clause or you'll get an error.

Finally, I'd suggest building your form, adding your chart, double-click
on the chart to launch MS Graph, and plunging right in with the MS Graph
Help file (table of contents). There's (surprise!) quite a bit of
helpful information. I also searched Graph Help for "xy" and found
several hints that you might find useful.

Good luck,

LeAnne

> Thanks for your help. I fully appreciate the time and effort that goes into
> helping others in these forums.
[quoted text clipped - 51 lines]
>>>>>the data, which is not what I need. A search of this forum didn't shed any
>>>>>light on the subject.

Reply to this Message

The Big Smelly Ogre - 31 Jan 2006 21:00 GMT

Okay, I'm getting a plot now. Thank you very much.

Now, I need to break the data down into series, with a different coordinate
set for each series. Something like this:

Rock Tree Flower
x y x y x y
1 3 1 9 2 4
2 1 2 6 2 8
6 4 2 7 5 2

Any ideas?

> BSO,
>
[quoted text clipped - 80 lines]
> >>>>>the data, which is not what I need. A search of this forum didn't shed any
> >>>>>light on the subject.

Reply to this Message

LeAnne - 01 Feb 2006 17:34 GMT

Hmm...nope, sorry. I mean, I can tell you how to do it in Excel, but MS
Graph via Access doesn't seem to allow that option directly; at least,
none that I can find. HOWEVER, it is possible to create such a graph in
Excel (plot the rock, tree, and flower as separate series, using a
different marker for each series) and then import that Excel chart
directly to your form via MS Graph. Create form, insert chart object
(doesn't matter what kind, or what source), double-click chart to open
MS Graph, go to the datasheet, click the Import File button, and browse
until you find the Excel worksheet containing the graph you want.
Import it, and MS Graph will bring in the chart AND the underlying data
sources, correctly arranged, to Access.

Good luck,

LeAnne

> Okay, I'm getting a plot now. Thank you very much.
>
[quoted text clipped - 93 lines]
>>>>>>>the data, which is not what I need. A search of this forum didn't shed any
>>>>>>>light on the subject.

Reply to this Message

The Big Smelly Ogre - 01 Feb 2006 17:47 GMT

Thanks again for your help. I already have Excel set up to plot my data. I
was just hoping to make things cleaner by putting it all into one application.

> Hmm...nope, sorry. I mean, I can tell you how to do it in Excel, but MS
> Graph via Access doesn't seem to allow that option directly; at least,
[quoted text clipped - 109 lines]
> >>>>>>>the data, which is not what I need. A search of this forum didn't shed any
> >>>>>>>light on the subject.

Reply to this Message

LeAnne - 01 Feb 2006 18:36 GMT

You're welcome!

L.

> Thanks again for your help. I already have Excel set up to plot my data. I
> was just hoping to make things cleaner by putting it all into one application.
[quoted text clipped - 112 lines]
>>>>>>>>>the data, which is not what I need. A search of this forum didn't shed any
>>>>>>>>>light on the subject.

Reply to this Message