Charts and Graphs in Access

[XelaIrodavlas]

Hi All,

This is going to sound really pathetic and I'm sorry to even ask. But does anyone have any good examples of Access Charts and graphs?

The thing is, I have never used Access for this function, always been much easier and simpler to export data into Excel and do all the number crunching/graph drawing in there - it's what Excel's good at.

But now I'm being asked to replicate the graphs of an excel spreadsheet to produce a dashboard style Form viewable in our Access database.

I've tried making a couple of these, but it's nowhere near as intuitive as Excel and frankly I'm getting stuck at some absurdly simple questions.

For instance:
- How (/can) you add trend lines onto Access bar/line Graphs?
- Can I show multiple queries on one graph?
- Is there a way to disable double clicking on a graph? (So my users can't get in and start changing things)

As mentioned I'm probably being lazy, I could work this all out eventually so sorry again to ask, but I could really do with a few nice example charts that I can meddle with to input my own data and copy/repeat for the twenty-odd different graphs I need to produce.

Any and all help appreciated

My thanks as always,
Alex S

 

[Ranman256]

i dont think access has trend lines,.
1 query, 1 graph
disable dbl-click by locking the report/graph.

If you must have more robust graphs, you can always use Excel to graph, where it draws its data from the Access query.

 

[apr pillai]

Few simple examples for creating Graph Charts in MS-Access:

http://msaccess-tips.blogspot.com/2007/08/ms-access-and-graph-charts.html
http://msaccess-tips.blogspot.com/2007/09/ms-access-and-graph-charts2.html

Yes, you can add trend lines in Access too. If you are a VBA Programmer you can do lot more things in Access by manipulating Chart Objects as well.

http://msaccess-tips.blogspot.com/2008/06/working-with-chart-object-in-vba.html
http://msaccess-tips.blogspot.com/2008/07/column-chart-and-vba.html
http://msaccess-tips.blogspot.com/2008/07/pie-chart-object-and-vba.html

You may download a database with all the VBA Code and running version of all the charts from the above pages.

 

[XelaIrodavlas]

Thanks apr pillai, this will give me plenty to look into

 

[The_Doc_Man]

Ranman, both Access and Excel open MS Graph, a tool you cannot open directly because it doesn't have its own GUI (that I've ever been able to find.) I found a reference from MSDN that suggests that the MS Graph application object is available to ALL other MS Office utilities. You can prove this for yourself by having task manager active, then open Excel and do a chart, then pull up Task manager to see what is in memory, then ditto for Access and a chart.

The trick is knowing the COM elements that are exposed by doing an Application object for graphs. I think where the confusion lies is that each Office utility's ribbon has whatever tools were built into the ribbon to tie into the underlying objects - but the ribbons don't have to match. If Excel has the more extensive set of ribbon-based objects, that merely suggests a more extensive ribbon, not that the interface isn't there. The trick is perhaps to try to decipher Excel's ribbon and make a new custom ribbon for Access... or to figure out the COM properties you need to know to bypass the ribbon. (And in fact, it is possible that deciphering the ribbon would give you that answer.)

A little web research suggests that with a chart application object, you have a collection called a .Series {you can have more than one} and that a .Series.Add function exists to allow you to add trend lines. I will agree that it is difficult to pull out the information you want about manipulating this line and it is NOT at all clear as to which .DLL references you need to add to get that feature. I blame MS for their typically obscured documentation on behind-the-scene objects.

However, another thought occurs to me. As long as you only need limited types of regression lines, there are ways to look them up online to get the formulas. Then compute the trend line parameters. It is ugly and involves lots of complex summations, but in the final analysis, the required operation can be a one-pass algorithm over your extant data. As a matter of fact, it could even be written as an SQL summation query over multiple fields including a couple of cases where the returned field is SUM(x*y) or SUM(x*x) or SUM(y*y), etc. Open a recordset for that summation query and extract the fields you want, THEN go back and create a second data series on the same chart. Use the formula you just created to create an "expected" value in the same scale of values that are on the "raw data" chart. Then plot that as a line graph on the same axes with the data markers turned off. If you are doing linear regressions only, you can do a non-smoothed line that will be whatever color you choose to make it stand out.

Sometimes if it is too hard to find the "right" way to do it, the work-around can do the job anyway. As to the formulas for regression lines, do a web search for "regression lines" and be prepared to be astonished at how many helpful sites there are on this subject alone. Wikipedia has articles and many colleges publish the required formulas.

 

[XelaIrodavlas]

Doc_Man I've just got to say thanks for the interesting post. Obviously this isn't exactly the answer I was hoping for (that would be = 'there's a big red button that does it for you!') but it is intriguing nonetheless.

If I understand correctly you're suggesting it would in theory be possible to create a custom ribbon that replicates the functionality of Excels charts/graphs, because they're using the same application object, obviously this would involve unending research and dev time, but would be great once mastered.

For me I'm afraid it's 'what works, must do' I was just kind of hoping i'd be able to do this without getting into too much coding. Ces't la vie!

Thanks all,

 

[stopher]

However, another thought occurs to me. As long as you only need limited types of regression lines, there are ways to look them up online to get the formulas.

Or use the Excel regression functions e.g. LINEST

Public Sub test()
    Dim oXL  As Excel.Application
    Set oXL = CreateObject("Excel.Application")
    
    
    Dim x()
    Dim y()
    Dim r()
    
    x = Array(1, 2, 3, 4, 5)
    y = Array(2, 6, 6, 6, 10)
    
    r = oXL.Application.LinEst(y, x)
    
    oXL.Quit
    
    For i = 1 To UBound(r)
        Debug.Print r(i)
    Next i

End Sub

So the output to the above code is 1.6 and 1.2 (the multiplier, m and constant, c in y=mx+c).

 

[The_Doc_Man]

Stopher, good catch. I don't use that feature that often from Access though I have done some historical trend plotting via Excel. But what I did was not suitable for line extrapolation at that level and I've never played with the estimators.