Running sum that restarts on each group

[doughnut]

Hi,

I would like to create in a query a field that calculates the running sum of
orders on each term.
I.e., each order with OrderDate, between BeginTernDate and EndTermDate,
should be added. the the running sum of the same term.
However, once we have orders from new term - the running sum must restart
from the first order of the second term.

The query is based on another query (instead of tables), that gathers all
relevant fields.
it should look like:

Term BeginDate OrderDate OrderAmount Running
36 Jan 2006 Jan 2006 100
100
36 Jan 2006 Feb 2006 150
250
37 Jan 2007 Jan 2007 200
200
37 Jan 2007 Feb 2007 150
350

Thank you for your help

 

[Michel Walsh]

SELECT a.term,
LAST(a.beginDate),
a.orderDate,
LAST(a.orderAmount),
SUM(b.orderAmount)

FROM myTable As a INNER JOIN myTable AS b
ON a.term=b.term AND a.orderDate >= b.orderDate

GROUP BY a.term, a.orderDate




I assumed there is one and only one OrderDate for each given Term, AND that
this OrderDate define the run (order the sequence of values to consider) of
orderAmounts

Hoping it may help,
Vanderghast, Access MVP

 

[doughnut]

It is helpful thanks, however, There are many orders in each term.
some of them with the same order date.
will that influnse results?

 

[Michel Walsh]

The GROUP and the ON clauses should describe the unique ordering sequence,
without dup. In fact, you should be able to describe the sequence, exactly,
just by its data (not by its 'position' in the table) to be able to get a
basic solution based on SQL.


Note that I assumed oderDate was a date_time field (not a string), so it
may be possible to have a day (in addition to a year and a month), or even a
time in it, and with that such precision, {team, orderDate} can be a valid
primary key candidate (or at least, we could define a UNIQUE constraint on
it). ONCE the running sum is computed, you can then FORMAT that full
date_time value to only show the year and the month, but do it only AFTER
having computed the running sum. Is that possible, in your case?


Vanderghast, Access MVP