Problems with Simple Cumulitive 'Running' Totals.

[Cosmonaut_99]

Hi,

I have a very simple table called "ABC Analysis Table_02", with 2 fields [Item] and [Max 12 Months Dmd Value]. The table is sorted by [Max 12 Months Dmd Value], descending.

I want to create a query using DSUM to calculate a cumulitive running total on field [Max 12 Months Dmd Value].

This query would involve no fancy break-downs, or sub-totals, and will simply show the cumulitive running values of [Max 12 Months Dmd Value] by [Item].

This will allow me to product a summary similar to a pareto analysis.

Al of the tips and advice that I can find on Google, Office tips, erc all involve extra unnecessary complications to the expression, (e.g. extra-break downs or running sub-total which I do not want).

I think the formula I want is something like :

Cumulitive: Format(DSum("Max 12 Months Dmd Value","ABC Analysis Table_02, <something ???? >)


Any help gratefully received.

C99.

 

[mahenkj2]

In one of the post of John Big Booty I found this link, is it useful to you.

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

 

[Cosmonaut_99]

Thanks Mahenkj2.

This is one of the examples I had already found on the net & tried, but it proved unsuccessful.

Both examples on the page include extra, unnecessary sub-totals or break-downs which complicate the expression, and are not what I want.

All I'm looking for is an expression on how to do a simple cumulitive total calculation on 1 field.

C99.

 

[plog]

I think the formula I want is something like :

Cumulitive: Format(DSum("Max 12 Months Dmd Value","ABC Analysis Table_02, <something ???? >)

Correct. Since you want it by item, the <something> is going to include that.

"[ITEM]='" & [ITEM] & "'"

However, Running Total implies you have an order to your data and you said the table has only 2 fields, neither of which are used for order. So, what is that order based on? If you had a date field called 'DateField' the rest of <something> would look like this:


"[ITEM]='" & [ITEM] & "' AND [DateField]<=#" & [DateField] & "#"

You need another field which you can order your data by.

 

[Cosmonaut_99]

Thanks Plog !

This is where I got stuck before.

I tried to add in an extra field to my table called [Extra_Field], and updated all row values to 1.

(The thinking behind this is that this would produce 1 level of running values, rather than lots of sub-total levels).

e.g.

Cumulitive: Format(DSum("Max 12 Months Dmd Value","ABC Analysis Table_02","[Item]='" & [Item] & "' AND [Extra_Field]<=#" & [Extra_Field] & "#"))


However, this still doesn't seem to work.
I get "#Error"s when the query displays.

 

[plog]

My example code worked only if the field you were using to order your data was a Date, thus your errors. I don't know what you thought you were accomplishing by adding an additional field, but if every value of it is the same in all rows, you've not imposed any order on your data via that field.

You need a field in your data on which you can define an order (1,2,3,4,5 or 10/1/2012, 10/2/2012, 10/3/2012 or A, B, C, D ).

 

[Cosmonaut_99]

Hmm

I was simply trying to apply your suggestion to fill the gap.
Maybe it's back to the drawing board, with a different method all together.

The attached image shows what I'm trying to achieve - e.g. a running total of [MAX 12 MONTHS DMD VALUE].


C99

 

[mahenkj2]

Based on the screen shot, I feel may be attached example is of interest to you. After your working if you find still not workable then attach the sample dB with example results.

The attached dB is on this forum only but I could not get who is the original poster for this.

 

[gemma-the-husky]

to be honest, it is possibly a bad idea to think that you do need a running total in the query.

how/where are you trying to see that running total?


the reason this is hard, is because a database deals with a set(s) of data. the set itself has a fixed number of elements, and therefore a totals query can easily derive the total of those elements - but the order of elements is both undefined, and unimportant - therefore a sequential process assigning a running total is very difficult to achieve.

eg - what happens (or should happen) to the running total if you re-sort the query on a different column(s)

as soon as a "fixed" sort appears - eg, as in a report then a running total is immediately available.