Running Sum

  • Thread starter Thread starter Todd
  • Start date Start date
Dear Todd:

You create a correlated subquery that sums all the preceding rows
within a group.

A group is a set of rows characterized by one or more columns.
Usually, the report is sorted first by these columns. Whenever the
value in any of them changes, the sum starts over from zero.
Otherwise, the whole set starts from zero.

It is essential that there be an overall sort on a set of columns that
are unique. Otherwise, several rows will add into the total
simultaneously. Only by completely controlling the order the rows are
sorted can you have a running sum that adds one row at a time.

If you give a query here that produces the results on which to base
this running sum, I can modify it to give the running sum as well. As
stated above, it should have a uniquely sorted order. You must also
specify on which columns any grouping is based so the running sum can
start over, assuming that is also desired.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
Thanks for your help.

Here is the SQL for a query that I'm trying to get the
running sum for. Right EXPR only returns the value of
Num200Needed for each record, not a running sum.

SQL:
SELECT ForecastTBL.SeqDate, ForecastTBL.WPB,
ForecastTBL.Num200Needed, Sum(ForecastTBL.Num200Needed) AS
EXPR
FROM ForecastTBL
GROUP BY ForecastTBL.SeqDate, ForecastTBL.WPB,
ForecastTBL.Num200Needed
ORDER BY ForecastTBL.SeqDate;

Can you help me figure out the running sum?

Thanks.
 
Dear Todd:

Here is the code for your query, using a correlated subquery to
perform the running sum:

SELECT SeqDate, WPB, Num200Needed,
(SELECT SUM(Num200Needed) FROM ForecastTBL T1
WHERE T1.SeqDate <= T.SeqDate)
FROM ForecastTBL T
ORDER BY SeqDate;

Please note that you can do this in a report without the query work,
and it is faster there. But if you really require it as a query,
there it is.

Notice that when more than one row has the same SeqDate then they all
add in simultaneously. That is because the ordering of the query is
only by SeqDate, so multiple rows with the same SeqDate are not in any
particular order, and cannot be distinguished. Adding another column
to the ordering of the rows and to the subquery so the ordering is
unique could change this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for the help. That was exactly what I was looking
for. By the way, I was aware that I could do the running
sum in a report, but in this case feel it will work for me
better in the query.
 
Back
Top