Cummulative counting in a query

[johan]

Somebody can help me out ?

Simple example:

I have one column in Table1 called [number] which is filled with
several values.
Now I create a query which need to show the records from [number] and
in an expressioncolumn the cummulative numbers.

Just like:
[number] [cum-number]
2 2
4 6
6 12
1 13

Is there an easy way of filling in an expression in the query which
shows the cummulative value ?
If not possible is there a simple module which can do it for me.
I prefer a query solution.

regards,
Johan

 

[Allen Browne]

As it stands, you have an impossible task. A cummulative sum means adding
the numbers from the previous rows, but your example shows no way to define
"previous." You will need to add a required and uniquely indexed field so
that 'previous' is defined.

If you have a primary key named ID that orders the records, you could use a
subquery to get the cummulative sum.

SELECT [number],
(SELECT Sum(Dupe.[number]) AS SumOfNumber
FROM Table1 AS Dupe
WHERE Dupe.ID <= Table1.ID) AS CumNumber
FROM Table1
ORDER BY ID;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

I'm guessing your field is not really called Number: that's a reserved word.
In any case, here's a list of the field names to avoid when designing
tables:
http://allenbrowne.com/AppIssueBadWord.html

 

[johan]

Thanks a lot,

regards,
Johan

 

[johan]

Thanks again, but.....

The solution won't fix my problem because I explained it not complete.
Herewith a new explanation of my problem.

My datafield with the values (I call it now 'value' instead of
'number' is related to a datafield with a date.
So, you get the columns:

ID DATUM VALUE CumSum (should be)
02 01/03/2008 10 10
01 01/05/2008 2 12
03 01/07/2008 5 17

As you can see, the CumSum should be based on the ascending sort of
the field DATUM.
For this case it's not possible to use the ID nr field because the
dates are set on a free basis per record.

What I like to have is a query which shows the Calculated CumSum based
on the sort-ascending DATUM.
If not (easy) to solve with a query, perhaps somebody can help me with
a module that's update automaticle the CumSum column.

I hope somebody can/will help me out.

ps.
If difficult to explain, you can also send me a little created file
with the table/query or even the module in it.
(that's perhaps better for me to understand because of my actual
Access knowledge level).

email: (e-mail address removed)

regards,
Johan

 

[Allen Browne]

You can still use a subquery with the date column in the WHERE clause:

SELECT Table1.[value],
(SELECT Sum(Dupe.[value]) AS SumOfValue
FROM Table1 AS Dupe
WHERE Dupe.Datum <= Table1.Datum) AS CumSum
FROM Table1
ORDER BY Datum;

This will work provided the dates are unique. If you have 2 values on the
same date, the results will not be correct (unless the date field also has
times that can distingish the order.)

 

[johan]

Allen,

Sometimes Access life looks difficult.
Thanks for your help. Cost some time and gives some troubles,
but......
now it works well based on your excellent help.

Thanks a lot.

regards,
Johan