Making Graphs in Access (Access 2002)

Topic

New Reply

Hi all,

The concept of graphing is absolutely horrible to me, but somehow I managed to make a graph that represents hours per million calculations. My X axis ends up being quite long, ie. a >50MM, b 50MM to 100MM, c 100MM to 150MM–and so on (the a, b, c is so that the categories will come out in the right order). I tried using the “tilt” to turn them at an angle, but when I go to view or print the report like that, the fonts are all messed up.

Has anyone dealt with this before? I’ve already lost about 2 days just on this silly thing.

Incidentally, I saw posts that said that Pivotchart was better than the internal Access thing, but I couldn’t get that to work at all. I could put a pivotchart on a form, but then I couldn’t pull the fields over into it. I couldn’t put one on a report at all.

TIA!

Reply | Quote

Replies

Try tilting the labels by 90 degrees up or down, making the text vertical. That usually looks fine.

Pivot charts are meant to be interactive, so a pivot chart on a report would be useless, if you could create one.

Reply | Quote

Ah, so THAT’s the difference between the pivots and the chart. Thanks!

The 90 degrees prints fine, but the labels are long so they’re insisting on tilt. Go management.

Do you happen to know if there is any way to make a chart out of a crosstab query? That is really what I’d like to do, but I can’t seem to find anything showing how to do that.

Reply | Quote

The chart wizard will create crosstab queries on the fly for you, but is not suitable for creating a chart based on an existing crosstab query. If you have a ready-made crosstab query, I suggest the following:

• Create a chart of the type you want, and base it on any simple table; it really doesn’t matter a lot what you choose here.
• When you’ve finished the wizard, set the Row Source of the chart to your crosstab query.
• Switch to preview mode to test the chart.
• Switch back to design mode and double click the chart if you want to fine-tune it.
  This is a nuisance, since you don’t see the real chart when you do this, but it can be done.
  [/list]Sometimes, it is easier to create the chart in Excel. It is much more user-friendly when it comes to editing charts. You can base it on data linked to an Access table or query.

Reply | Quote

Hi Hans,

Here’s the issue with the crosstab query: I am creating a crosstab with average hours per size of store by region. Now, if I do this with a simple query, it’s fine, but what I’m trying to do is compare region to national. So I’d like one bar that shows my region average, then a second bar that shows the national average. If I do this with a simple query, I can’t do the national in the same query. However, if I write a crosstab query, I can have a row that shows the national averages.

From what I’m hearing, I _should_ be able to do this in the graph as well, I just haven’t figured out how to do it. Unfortunately, I’m as newbie in Excel as I am in Access’s graphing function. But the bonus on the Excel graph is that I AM able to make a graph out of my crosstab query (and it actually looks quite nice ;-). What I can’t do is put my excel graph into access….

Anyway, thanks for the advice, I will take my newbie self back to the drawing board on this part for now

Reply | Quote

You could create a hyperlink in Access to the Excel chart.

To get the Excel chart into an Access report, here is a rather roundabout method, but it does work:

• Create a chart in Excel based on data from Access. It can be a pivot chart based directly on data in Access, or a standard chart based on a table in Excel linked to a table or query in Access.
• Copy the chart to the clipboard.
• Use Paste Special as Link to paste the chart into your report.
• Updating will not be entirely automatic. You will have to refresh the chart or linked table in Excel if the data in Access have changed.
  [/list](I also tried to insert an Excel chart object into a report and set its row source to an Access query, but this crashed and started the Installer…)

Reply | Quote

Hey, that’s pretty cool!

It does seem to work! The downside is that I now need to keep an excel file with my front end…but any way that gets you there!

Thanks for the tip, Hans!

PS. THe inserting chart thing went wacko on me, too. It worked once, but then it wouldn’t update, and then it started acting all funky….

Reply | Quote