We help IT Professionals succeed at work.

MS Access 07 How to create a Pareto Chart in a report

I have successfully created a chart with the following query.

SELECT Sum(TBCSum) AS [Torch/Burn/Clamp Marks], Sum(MissingWelds) AS [Missing Welds], Sum(AirInWeld) AS [Air In Weld], Sum(HoleInWeld) AS [Hole In Weld], Sum(HammerMarks) AS [Hammer Marks], Sum(Rewelds) AS [Re welds], Sum(TackMarks) AS [Tack Marks], Sum(TorchMarks) AS [Torch Marks], Sum(MissPartsProd) AS [Missing Parts Prod], Sum(WBPartsInstalled) AS [Wrong/Bad Parts Installed], Sum(MissPartsEng) AS [Missing Parts Eng], Sum(BurnThrough) AS [Burn Through], Sum(PartsInWrongLoc) AS [Parts In Wrong Location], Sum(Blend) AS [Weld Blend], Sum(Guber) AS [Weld Guber], Sum(Washout) AS [Weld Washout]
FROM WeldFrequency;

I'm basically getting the sum of all the fields in the table.  How do I make this into a Pareto chart?

Comment
Watch Question

This article relates to 2002 - may also work in 2007

Author

Commented:
I have read that article and wasn't able to get it to work.  I'll give it another try I guess.  

Author

Commented:
I get to this step:  Click the Axis tab. Under Plot series on, click Secondary Axis, and then click OK.

And the it is disabled with Primary Axis selected.  I'm not sure what I'm doing wrong.  

Author

Commented:
Any other suggestions?  The example in the link doesn't really apply to my situation as I'm not using currency or percentages.  And when going through the steps and changing to Number rather than Currency (as they suggest), when I go to change the Plot Series to Secondary Axis it is disabled.  
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
...This is why it is always best to post a sample DB, ...
Does your query produce the series in "Declining" order as is needed for a true Pareto chart?
Is each series the same color or a different color...?

Author

Commented:
I'm not sure how to produce the series in Declining order with the query I'm using.  

Each Series is the same color.

boaq, I have attached a sample copy of my DB.  Let me know if you need anything more.

I appreciate the help.  
Database1.mdb
MIS Liason
SILVER EXPERT
Most Valuable Expert 2012
Commented:
First I will say that Access cannot, on it own, generate a true "Pareto chart", as I have seen them represented here:
http://en.wikipedia.org/wiki/Pareto_chart

Though you can add a series to a secondary Axis, I don't know of a way to set  the scale of the second axis to reference 0-100%

The structure of your data also presents a challenge.
Because your data is in a Row with multiple fields, (instated of in a column with multiple records)
it is not easy to sort the series in descending order as seems to be required for a Pareto_chart

Going deeper...

Even if your data was structured optimally, you would still have to get a running sum of the totals "In Descending Order", ...to use as the source for the "Line"
(Now, I am sure this can be done in SQL, but I had to use a recordset to make a temp table.)

*Whew*

All this being said, here is the best I could come up with...
The top chart is the running sum series simply changed from a bar chart to a line chart.
The bottom chart is the Running sum series plotted on a secondary Axis

Perhaps I missed something simple, ...perhaps if I could invest more time,  I could probably get this closer to a true Pareto Chart,...
But again, for now, this is the best I could do...

Examine it careful and fully, ..and have fun...!

;-)

JeffCoachman
EEQ27429457--Access--Reports--Pa.mdb

Author

Commented:
I'll take a look at it right away tomorrow morning Jeff.  Thank you!
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
;-)

Author

Commented:
Thanks Jeff.  I appreciate the help!
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
...Actually an interesting question...

I'll play around with this for a while in my spare time...

If I come up with anything, ...I'll post it here