Charting Options

T

theburnetts

I need to be able to create a line chart (graph) on an Access form.
The X-axis should be dates. There should be two different Y axes for
two different series. I have tried to use the built in graphing tools
that comes with Access but it doesn't seem to support what I want to
do. It seems to require that each series have a value for every date
on the Y axis. My data set will be quite different than that. Below
is an example of the data that I want to graph.

Series 1
Date: 1/1/2004 Value: 23.5
Date: 1/7/2004 Value: 31.8
Date: 1/10/2004 Value: 30.9
Date: 1/14/2004 Value: 18.34
Date: 1/15/2004 Value: 20.5

Series 2
Date: 1/5/2004 Value: 345.34
Date: 1/7/2004 Value: 355.0
Date: 1/11/2004 Value: 378.3
Date: 1/16/2004 Value: 333.7

I would like to plot both of these series on the same graph with dates
across the bottom and series 1 on the left Y axis and series 2 on the
right Y axis. I have tried using the Chart Wizard in Access but it
doesn't seem to work. I have also tried using MSGraph but it doesn't
seem to work either. Are there any options that are built in to Access
or Office that I could use without having to purchase a 3rd party
ActiveX control. Ideally I would like to be able to distribute the MDB
file without having to distribute ActiveX controls or OCXs also.
Thanks,
Corey
 
T

Tom Wickerath

Hi Corey,

I think your best bet would be to investigate automating Excel from within Access. You can turn
on the macro recorder in Excel, and create the chart with the proper formatting. This will
produce the code that is required to create the chart, which you can then use with some
modification in Access. I believe you can also create a template file in Excel, and then open the
template in Access code and push the data to it. This might be the easier bet versus creating the
chart from scratch in code.

See the following KB article for a how-to sample:

Using Automation to Create a Microsoft Excel Chart
http://support.microsoft.com/?id=202169


Tom
___________________________________


I need to be able to create a line chart (graph) on an Access form.
The X-axis should be dates. There should be two different Y axes for
two different series. I have tried to use the built in graphing tools
that comes with Access but it doesn't seem to support what I want to
do. It seems to require that each series have a value for every date
on the Y axis. My data set will be quite different than that. Below
is an example of the data that I want to graph.

Series 1
Date: 1/1/2004 Value: 23.5
Date: 1/7/2004 Value: 31.8
Date: 1/10/2004 Value: 30.9
Date: 1/14/2004 Value: 18.34
Date: 1/15/2004 Value: 20.5

Series 2
Date: 1/5/2004 Value: 345.34
Date: 1/7/2004 Value: 355.0
Date: 1/11/2004 Value: 378.3
Date: 1/16/2004 Value: 333.7

I would like to plot both of these series on the same graph with dates
across the bottom and series 1 on the left Y axis and series 2 on the
right Y axis. I have tried using the Chart Wizard in Access but it
doesn't seem to work. I have also tried using MSGraph but it doesn't
seem to work either. Are there any options that are built in to Access
or Office that I could use without having to purchase a 3rd party
ActiveX control. Ideally I would like to be able to distribute the MDB
file without having to distribute ActiveX controls or OCXs also.
Thanks,
Corey
 
T

theburnetts

Tom,

Thanks for the reply. I have looked at using Excel also. However it
seems like the Chart Wizard in Excel is the same Chart Wizard from
Access and has the same limitations. It doesn't seem to support the
kind of graph/chart that I want to do using the data from above. It
seems to require that both series (for the two different Y axes) have
data points for every value on the X axis. In other words, I can't
even get it to work in Excel either.

Thanks,
Corey
 
T

Tom Wickerath

I suggest going through Jon Peltier's web site to see if you can learn how to build the chart you
need in Excel.
http://www.peltiertech.com/index.html.

This example looks like it might be close to what you need:

Series with Distinct X and Y Values
http://www.peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#manyxy

Here is another link that can lead to hours of exploration!
http://www.contextures.com/charts.html

If you can't find a chart in Excel that will work, then I'm afraid you are out of luck, without
resorting to some expensive add-on. The chart wizard in Access is not even close in capability to
the chart wizard in Excel.

Tom
________________________________


Tom,

Thanks for the reply. I have looked at using Excel also. However it
seems like the Chart Wizard in Excel is the same Chart Wizard from
Access and has the same limitations. It doesn't seem to support the
kind of graph/chart that I want to do using the data from above. It
seems to require that both series (for the two different Y axes) have
data points for every value on the X axis. In other words, I can't
even get it to work in Excel either.

Thanks,
Corey
 
Top