Creating a running sum query 
Author Message
 Creating a running sum query

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



Mon, 20 Aug 2001 03:00:00 GMT  
 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
|
|



Tue, 21 Aug 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Running Sums with queries

2. How to do Running Sum in a Query

3. Article: How to create a running sum on a form

4. Need help with joined table sum query

5. SQL:How do i query sums???????

6. funny Sum statement in msaccess query

7. how to sum fields that are not included in the sql query

8. Getting the value of a Sum() in a SQL query

9. Accessing SUM results from a SELECT query

10. How to do a Running Sum with Only Negatives

11. Running sums on a FORM??

12. Running sum

 

 
Powered by phpBB® Forum Software