x-y plots

[The Big Smelly Ogre]

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.

 

[LeAnne]

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

 

[The Big Smelly Ogre]

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.

 

[LeAnne]

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:

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.

LeAnne

 

[The Big Smelly Ogre]

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

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

hth,

LeAnne

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.

LeAnne

The Big Smelly Ogre wrote:

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.

 

[LeAnne]

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.

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

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

hth,

LeAnne

The Big Smelly Ogre wrote:

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.

LeAnne

The Big Smelly Ogre wrote:


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.

 

[The Big Smelly Ogre]

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,

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.

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

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

hth,

LeAnne

The Big Smelly Ogre wrote:


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.

LeAnne

The Big Smelly Ogre wrote:


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.

 

[LeAnne]

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.

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,

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.

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

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

hth,

LeAnne

The Big Smelly Ogre wrote:



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.

LeAnne

The Big Smelly Ogre wrote:



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.

 

[The Big Smelly Ogre]

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,
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.

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,

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


The Big Smelly Ogre wrote:

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

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

hth,

LeAnne

The Big Smelly Ogre wrote:



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.

LeAnne

The Big Smelly Ogre wrote:



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.

 

[LeAnne]

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.

:

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.

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,

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


The Big Smelly Ogre wrote:


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

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

hth,

LeAnne

The Big Smelly Ogre wrote:




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.

LeAnne

The Big Smelly Ogre wrote:




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.