Problems with Simple Cumulitive 'Running' Totals. (1 Viewer)

Cosmonaut_99 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 24, 2012

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 · Oct 25, 2012

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.

Pat Hartman · Oct 25, 2012

You simply cannot do a running total in a query unless you have a unique id that can be used to control the DSum() or Join. Both methods require it. Think about it logically. When you created the example in the .jpg, your data had a particular order. It was that order that dictated which rows were summed. Without a unique identifier, there is no way to tell the query how to order the rows so the running sum can be calculated.

An alternative is to use a report. A report processes a set of data sequentially in the order in which the rows are presented. So sorting on Item will result in a logical grouping although with a large set of data, it is possible that the individual records will be in a different order today than they are tomorrow. If that doesn't matter to you, just add an autonumber to the table and use that in the order by clause. Rows will be presented grouped by Item but always in sequence by the autonumber within that. Repeatable results are not possible without a unique identifier.

gemma-the-husky · Oct 25, 2012

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.

Problems with Simple Cumulitive 'Running' Totals. (1 Viewer)

Cosmonaut_99

Registered User.

mahenkj2

Registered User.

Cosmonaut_99

Registered User.

plog

Banishment Pending

Cosmonaut_99

Registered User.

plog

Banishment Pending

Cosmonaut_99

Registered User.

Attachments

mahenkj2

Registered User.

Attachments

Pat Hartman

Super Moderator

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread