RunSum

[Tom]

Does anyone know how to create a "Running Sum" in a query? If a have a
field called Amount, I want the RunSum to show the sum of previous record's
RunSum plus current record's amount.

Amount RunSum
999 999
333 1332
0 1332
20 1352
100 1452


I appreciate any feedback that might solve this problem.
Tom

 

[Michel Walsh]

Hi,


The records are not ordered among themselves, unless THERE is DATA that
defines the order. *if* you have a third field, like a dateTime stamp, that
uniquely defines the ordering of your data, then:


SELECT a.DateTime, LAST(a.Amount), SUM(b.Amount) As RunningSum

FROM myTable As a INNER JOIN myTable As b
ON a.DateTime >= b.DateTime

GROUP BY a.DateTime




You can note that the join makes almost all the job by itself, by looking at
the data before aggregation:


SELECT a.DateTime, a.Amount, b.DateTime, b.Amount
FROM myTable As a INNER JOIN myTable As b
ON a.DateTime >= b.DateTime
ORDER BY a.DateTime, b.DateTime


Once that data is available, it is simple to see the required GROUPing.
Since there is only one record by dateTime, we can use MIN, MAX, SUM, or
FIRST instead of LAST.



Hoping it may help,
Vanderghast, Access MVP

 

[Tom]

Michel:

Thanks for the feedback! Okay, just 2 follow-up questions...

Yes, I do have another field (similar to DateTime) by which I'm sorting in
Desc order.

****
SELECT a.DateTime, LAST(a.Amount) AS Amount, SUM(b.Amount) AS RunningSum
FROM myTable AS a INNER JOIN myTable AS b ON a.DateTime >= b.DateTime
GROUP BY a.DateTime
ORDER BY a.DateTime DESC;
****

Now, based on the desc order, the RunSum is flipped-flopped and show the max
total as the RunSum of the 1st record... and then it goes down towards 0.00.
Any ideas how to change that?

Also, once I close the saved query, I get an Access dialog box that
indicates that this query cannot be displayed in Design View. I need to do
a bunch of joining of tables and I'm not too comfortable doing this in the
SQL view. Do you know of a way to show your query in design view so I can
easily manipulate the query?


Thanks,
Tom

 

[Tom]

Michel:

Okay, the 1st "problem" was actually easy to fix... I just switched the sign
from >= to <=.

Still, I'd appreciate if you have any final comments on the issue "clicking
Design View" won't open up the design view.

Tom

 

[Michel Walsh]

Hi,

Changing the ORDER BY to ASC rather than DESC should do the trick for the
first problem.

For the second problem, you can re-edit the SQL statement to be:

SELECT...
FROM myTable As a, myTable As b
WHERE a.DateTime >= b.DateTime
GROUP BY a.DateTime
ORDER BY ...


and, under that syntax, you should be able to use the grid editor.



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

The query grid can only show joins that use the equal operator. What you can do
is TEMPORARILY change the >= to equal. That will allow you to switch to the
query grid. Add in your other tables, fields and joins. When you finish,
switch back to SQL view and change the = back to >=

 

[Tom]

Thanks!

--
Thanks,
Tom

The query grid can only show joins that use the equal operator. What you
can do
is TEMPORARILY change the >= to equal. That will allow you to switch to
the
query grid. Add in your other tables, fields and joins. When you finish,
switch back to SQL view and change the = back to >=