Struggling with DSum

[Kodash]

Hi Guys,

I was wondering if someone may be able to help me solve what is hopefully a really easy syntax problem.

I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)

I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:

Reason Count Per Expr1
A 35 47.9 Random Numbers
B 11 15.1 "
C 10 137 "
D 9 12.3 "
E 7 9.6 "
F 1 1.4 "

Expr1: DSum("Per","Rwk_Pareto","[Per]<=" & [Per] & "")

Can anyone suggest where I've gone wrong? Thanks In Advance

 

[Frothingslosh]

In the example data you posted, it looks like Per is a number. In that case, Per does not need to be surrounded by quotes in the WHERE clause, so instead of this:

DSum("Per","Rwk_Pareto","[Per]<=" & [Per] & "")

try this:

DSum("Per","Rwk_Pareto","[Per]<=" & [Per])

 

[Kodash]

Excellent Frothingslosh,

That worked Beautifully, It was working backwards for a minute then i realised i made a **** up in my original expression "<" instead of ">"

I just need to figure out how to draw my chart now

 

[Kodash]

I've been playing about with my dataset, to try and draw some more charts. I seem to be having a problem with DSUM grouping values and then adding their combined sum. e.g

ErrCode: Count Percent Cume
9 6 30 30
5 4 20 70
2 4 20 70
PP 1 5 100
CP 1 5 100
7 1 5 100
3 1 5 100
11A 1 5 100
11 1 5 100

The 2, 4 Counts have added to 40% and the 6, 5's To 30%

The Code i'm using is:

Cume: DSum("Percent","pareto_NCG","[Percent]>=" & [Percent])

I assume there's something wrong in the Criteria section, cant seem to get my head around it. Any ideas?

 

[namliam]

if the 20 is the actual percent it would seem logical if you are totaling everything <= 20 that this will "sum" both 20% together since they are both 20%

Same way that all six of the 5 percents were added together from 70 to 100%

 

[Kodash]

Hi Namliam,

I'm trying to defy the logic I need it to incrementally sum the values rather than add both 20%'s. I'm trying to make this:

The pink line is produced by the DSUM function, I got lucky in this chart that there were no duplicate count values

 

[namliam]

yeah well you will probably need to make like a rownumber to force a unique order and sum from that....
Running sum is a notorious nightmare in access

Even in Office 365 they havent made a "proper" solution for it (as far as I know)