Rolling tally through query not correct using dsum

[munkee]

Right a bit of a headache here.

I basically wanted to create a cumulative total of a percentage through a query.

To put that in a more physical way:

NCTYPE   Freq   cpercent
x        2      2/9
y        3      5/9
z        4      9/9

Where the / signifies a divide to get a percentage. The 9 is just the total of the frequency.

Anyway microsoft provide a way of doing this from their article here:

http://support.microsoft.com/kb/304180

However, their formula fails to work in one particular scenario.

The ms formula is:

CumPct: DSum("[ProductSales]","tblSalesByCategory", _
"[ProductSales]>=" & [ProductSalesTotal] _
& "")/DSum("[ProductSales]","tblSalesByCategory") 
 

Which is basically using DSum on the fields to produce the cumulative percentages. The issue is if you have two values that are both the same the [ProductSales]>= will not work correctly.

Here is an example:

Type    freq    cpercent
x       2       100%
y       2       100%

This is also incorrect for:

Type   freq  cpercent
x      2     0.5
y      1     1
z      1     1

The problem is that because it is looking for a value >= to be running the DSUM it just cant do it when there are two of the same values.

Does anyone have a suggestion of how this can be done correctly?

[Steven Kogan]

You would need a tiebreaker when the two values are equal. If the two values are equal you could use a primary key to decide which to include.

The formula would be something like this:

CumPct: DSum("[ProductSales]","tblSalesByCategory", _ 
"[ProductSales]>=" & [ProductSalesTotal] _ 
& " AND [PrimaryKeyField]<"&[PrimaryKeyField])/DSum("[ProductSales]","tblSalesByCategory")  

[Steven Kogan]

Hmmm... That would exclude the current amount. Try:

CumPct: ( [ProductSalesTotal]+DSum("[ProductSales]","tblSalesByCategory", _  
"[ProductSales]>=" & [ProductSalesTotal] _  
& " AND [PrimaryKeyField]<"&[PrimaryKeyField]))/DSum("[ProductSales]","tblSalesByCategory")

Let me know if it works properly.

[munkee]

Steven,

I have just given the code a go.

I'm slightly changing the names of fields etc to reflect what I am doing (I'm not doing product sales that is just the microsoft example.) and so far it has given me better results but not fully correct.

At the moment I am getting the equivalent of the following with your 2nd post code:

uniquekey  type  freq  cpercent
1          x     2     
2          y     2     1

This is all correct apart from the blank first row which should be 0.5, a lot closer than previous methods though.



Edit:

I added a bit more data to confirm what was happening:

NCtype    TotalNC        CumP1ct
x         2       
y            2         0.666666667
z         2           1

[munkee]

Bingo :D

Thanks for the help Steven I've managed to get it right in my initial test.

To get it working right I had to change a couple of parts to your formula so i get:

CumP1c1t: (DSum("[CountOfNCType]","tblParetoEvent","[CountOfNCType]>=" & [TotalNC] & " AND [ID]<=" & [ID]))/DSum("[CountOfNCType]","tblParetoEvent")

In your formula with the microsoft example it would be:

CumPct: ( DSum("[ProductSales]","tblSalesByCategory", _   
"[ProductSales]>=" & [ProductSalesTotal] _   
& " AND [PrimaryKeyField]<="&[PrimaryKeyField]))/DSum("[ProductSales]","tblSalesByCategory") 

Just needed to remove the first addition and sort out the key field criteria. But you definitely sent me on the right path.

Thanks for the help!

ps: If you can make the addition in one of your posts I'll put you down as best answer since you knew it needed a unique tiebreaker in this situation and my posts dont really follow on right.

[Steven Kogan]

Adding a tiebreaker when the two values are equal solves the problem. If the two values are equal you could use a primary key to decide which to include.

Thanks for fixing up the formula so it works properly:

CumPct: ( DSum("[ProductSales]","tblSalesByCategory", _    
"[ProductSales]>=" & [ProductSalesTotal] _    
& " AND [PrimaryKeyField]<="&[PrimaryKeyField]))/DSum("[ProductSales]","tblSalesByCategory")