Running Sum

[Tim]

How do i take the #'s from one column and post them to another column as a
running sum. Example ROW # 1 - Column #1 called Extender the first entry is
15 - i want that # transferred to the next Column #2 called Accum. then when
i add another entry on ROW #2 - Extender Column - 20 - i then want Accum
Column to have the sum of Row 1 & 2 Extender column which the Accum # should
now be 35

 

[Michel Walsh]

In a database, you have to relay on data and NOT on its position. So, if you
have, as example, a date time field, call it stamp, no dup, which indicates
the ORDER of the rows among themselves, then (in a query, in SQL view):


SELECT a.stamp, LAST(a.valueToSum), SUM(b.valueToSum)
FROM yourTableNameHere AS a INNER JOIN yourTableNameHere AS b
ON a.stamp >= b.stamp
GROUP BY a.stamp


will do. As exemple, if your table has the data:

yourTableNameHere ' table
stamp valueToSum ' fields
2008.01.01 1
2008.02.02 22
2008.03.03 -5
2008.05.07 11



then, the result will be


2008.01.01 1 1
2008.02.02 22 23
2008.03.03 -5 18
2008.05.07 11 29



Vanderghast, Access MVP

 

[Ken Sheridan]

You'll need a basis for ordering the rows first. A unique date/time value is
best as, assuming each row is inserted individually rather than in batches,
this can be automatically filled with the date/time at which the row is
inserted by giving it a DefaultValue property of Now(). You might think an
autonumber column would suffice, but that's only guaranteed to insert unique
values, not necessarily sequential ones, so is not totally reliable.
Assuming a DateTimeStamp column or equivalent means of ordering the rows
you'd Sum the values up to and including the current row in a subquery:

SELECT DateTimeStamp, Extender,
(SELECT SUM(Extender)
FROM YourTable As T2
WHERE T2.DateTimeStamp <= T1.DateTimeStamp) AS Accum
FROM YourTable AS T1
ORDER BY DateTimeStamp;

Note how the subquery is correlated with the outer query on the
DateTimeStamp column by giving the two instances of the table aliases T1 and
T2.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

How do i take the #'s from one column and post them to another column as a
running sum. Example ROW # 1 - Column #1 called Extender the first entry is
15 - i want that # transferred to the next Column #2 called Accum. then when
i add another entry on ROW #2 - Extender Column - 20 - i then want Accum
Column to have the sum of Row 1 & 2 Extender column which the Accum # should
now be 35

You cannot and should not do so in a Table (since you posted in Queries I'm
guessing you know this...). See the other responses for a query solution; but
if you just want this running sum to appear on a Report, you can put two
textboxes on the report labeled Extender and Accum. Both of them should have
Extender as their control source; set the Running Sum property of the Accum
textbox to "Over All" (or "Over Group" if you want subtotals by some other
grouping field).