Creating a running sum query
Hi Bill
You can try something like this
SELECT tblTest.Period, tblTest.Account, (SELECT SUM(Amount) FROM tblTest AS
t WHERE (t.Period <= tblTest.Period AND t.Account = tblTest.Account)) AS
RunSum
FROM tblTest
ORDER BY tblTest.Account, tblTest.Period;
This is data in Table tblTest
Period Account Amount
1 200 30
1 100 150
2 200 40
2 100 200
3 200 50
3 100 75
This is the result I got when running the query.
Period Account RunSum
1 100 150
2 100 350
3 100 425
1 200 30
2 200 70
3 200 120
Hope it'll help.
--
-------------------------------
Jan Bulr
|My table has 3 fields, ActivityPeriod,AccountNumber,ActivityAmount. I'm
|trying to create a query which returns the running sum of each
|AccountNumber, grouped by period. i.e.
|Table
|ActivityPeriod - AccountNumber - ActivityAmount
|1 100 $150
|2 100 $200
|3 100 $ 75
|Query
|ActivityPeriod - AccountNumber - RunSumActivityAmount
|1 100 $150
|2 100 $350
|3 100 $ 425
|
|Thanks for the help,
|
|Bill Carlson
|
|
