In over my head RE: charts

message from Averil Pretty on 7 May 2004

Hello,

As the subject suggests, I'm in a little over my head
here. I have to have this working for a meeting on Monday
(aaargh).

I have built a database that works great as it is but I
need some extra functionality. I have posted previously
about a few aspects of it and received fantastic
responses, so thank you all!

For now, this is basically what my database does:

Imports data from excel. This data is generated from
loads of formulas based on Scores which are also imported
into the database. Now, I know this isn't the best way to
do it because storing the data where it should just be
calculated is redundant - however it was way too complex a
task for me to create all the queries etc in the time
frame I had.

From forms this data can be viewed graphically, i.e. the
user can select a name and then view that persons' graphs
for Traits, Behaviours, Role Potential etc. These graphs
are currently line graphs where the datasource is from a
query. The graphs display as having the x axis from 1 to
100.

What I also want to be able to do is display the
individuals' graphs against an average (have done the
query for the average) in a line graph. However, whenever
I try to use the wizard it creates it so the x axis moves
to be the y axis (note, also does the same when trying to
change the existing bar graph to a line graph).

I have played around with pivot charts and see that you
can "flip" the chart but can't see how you can do this in
a form or report.

Previously in Excel I could create this, but I understand
from a previous response that even though Access and Excel
use the same charting engine, Excel is superior in how you
can manipulate the charts. It was suggested that in code
I open Excel, export the data to Excel, create the chart,
make the chart an image, import it back into Access then
place where needed. But, in all honesty, I haven't had
time to find the books suggested to me about how I can do
this. and I have been searching the Internet to no avail.

The other thing I would like to be able to do is layer
chart over chart. In excel I can show an individuals
scores in one line graph and behind that have a stacked
bar graph showing the standard deviation range generated
from the averages.

I wanted to move away from Excel so that I can present the
client with a single product/file. I couldn't do this
with Excel as other aspects of the database include
storing written summaries, strengths, motivators, areas of
developmental opportunities etc. for each individual.

Should I just give up now and find a work-around??? I'm
thinking that for this project, I can manually create all
the necessary graphs in Excel save them as images (oh
dear, there goes my weekend!) then just have the form or
report pull them up when and where necessary. The problem
is that my boss loves this idea and is planning on
presenting it to other clients, but I wouldn't want to do
this each time!

Any input or ideas is much appreciated. Thanks in advance!

Averil

p.s I'm due to take a Access/VB course soon so I wont have
to bother everyone so much and I try to help out where I
can (in fact am working on a problem for a fellow in the
Excel group as we speak).

Archived message: In over my head RE: charts (Microsoft Access)