Graphing/Charting - Access vs. Excel

[Paul H]

We are debating incorporating some excel charts and reports into an Access database that populates the table used by Excel as a datasource. I have managed to develop a graph on a form that resembles the Excel chart, but without all the bells and whistles. So far I have found the Access chart wizard to be much less friendly than Excel. In addition I want to set the text in the title and axis legends based on values on the form. I have not been able to find a name or list of properties for the chart I created so I'm not sure how to go about it.



My question is, "Is Access up to the task?". The motivation is to have the whole application in a single program. I'm not sure it is worth the trouble. Advice and/or some good resource material would be appreciated.

[June7]

The chart edits you want to do require VBA code. I find that Access graphing is less versatile than Excel. However, I was determined to eliminate Excel as a component of my data management system. In one case, I have to use four overlayed graphs to replicate what Excel could do with one. Review this thread http://forums.aspfree.com/microsoft-...hs-445065.html

[Paul H]

Thanks J7, unfortunately our firewall blocks me from accessing your project, but I am reading the link regarding chart formatting. I'm not optimistic about getting up to speed in the time I have available, but knowledge is power (and money) so it is worth pursuing.

I found the chart property sheet, which is helpful, I found the Chart Objects box, and of course the tabbed property windows for each object. I'm not up on the syntax to refer to these objects and properties. That is going to be key. Further reading should help.

Here's a quick question. In Excel you can hover over a datapoint to get its precise x and y axis values. Can Access do the same? I think not.

Thanks for your reply.

Paul

[June7]

Do you have another computer (home, friend?) that can download the file?

No popup on hover in Access, at least I never noticed it. I built graphs only on reports, not forms but doubt form would be any different.

[Paul H]

Surely your not suggesting I work from home? "Heavens to Murgatroyd"!

Uh, yeah, I'll look into it. I'm finding more reasons not to do this, but now it's a challenge. I suppose I could put a toggle button on the form to turn on data labels on the "Y" axis.

I have a follow up. I made a change on the chart to have the axis cross at a certain date value which worked fine for my example, but now that I've changed the parameter it doesn't work. I can't find where I did that now. I want to reset it to automatic. Can you help?

[Paul H]

Here's a followup on this subject. I haven't downloaded your project yet June7 , but I did find the sample database on one of your links. That database contains a storehouse of properties. I was able to lift a couple lines of code I needed that worked perfectly. There was virtually no help available from Access on this, no list of properties, no cascading autofill ins, nothing. I'll be able to search this sample database for ideas, but is there any comprehensive reference material on these properties and values? I have a hard time understanding why Access is so user unfriendly in this regard.

[June7]

I haven't found a comprehensive reference for programming graphs. It's an on-line scavenger hunt every time I have to figure out something new, which has been a while. Fortunately, when I first started building graphs in Access, had a paid consultant on hand to get me on right path. He taught me about UNION queries. There is no designer or wizard for UNION query, must type into SQL View window of the query designer. Access Help does have a reference about them.

Because of my data structure, all of my graphs require UNION queries to manipulate data so graphing is possible. It's because I didn't take another step in normalizing data. It is more convenient for most of my data entry and output. Only the graphs were complicated. It is a balancing act between normalizing and ease of data entry/output. This is my compromise. If you examine my db closely, you will find couple other compromises.

[Paul H]

I'm stumbling my way through this using trial and error. It's a great victory when I get it right. Would I recommend this to most people? No, but I my "peeps' like having this project consolidated into one application. If they are happy, I am happy. Excel is by far the easier tool to use though. Thanks for the pointers.

Paul

[June7]

Oh, did you figure out how to change the settings for axis crossings?

[Paul H]

Yes I did. Just select the axis, right click, Format Axis and set the Scale intervals to automatic. Of course it's always simple when you know what to do.

I'm going to reset this thread as unsolved, just to keep the discussion open. I'm not out of the woods yet and I think it's probably helpful to others or at least serves as an object lesson on what not to do. I'll post a few progress reports as I enter "uncharted" waters.

[Paul H]

I was going through a parallel effort by my coworker in Excel and immediately recognized the syntax generated by the macro recorder as he manipulated chart properties. With a few exception they are the same. In addition, Excel Help has a list of chart obects, members, properties, methods and syntax. The quickest way is probably just to record a macro changing the same property you need to use in Access and then adapt the code.

How about that?

[Paul H]

I found this very helpful thread on the MSDN forum. If you add a reference to the Microsoft Graph 14.0 Object library, the Object Browser will show all the graph objects, properties and methods. In addition the visual basic editor will assist in code writing with properties, methods and some syntax help. A key to getting this to work properly is to dim a variable as a Chart and then to set you the graph.object as follows.

Dim grph as Chart

Set grph = me.MyChart.Object

Then your off to the races.

This is a solved as it's going to be.

Good luck,

Paul