CODEWITHSUNDEEP
sqlms-accessms-access-2010
user3187012
user3187012

Reputation: 57

Creating a cumulative sum column for account activity

I have a Table which has the the fields Customer, Loan_Balance, SumOfAmount & Pstng_Date.

What I want is:

So the result I am looking for is the column Cum_Balance:

+--------+------------+-----------+----------+-----------+
|Customer|Loan_Balance|SumOfAmount|Pstng_Date|Cum_Balance|
+--------+------------+-----------+----------+-----------+
|Macey   |$0.00       |$21,545.59 |10/01/2014|$21,545.59 |
+--------+------------+-----------+----------+-----------+
|Jones   |$54,334.15  |-$21,197.89|10/01/2014|$33,136.26 |
+--------+------------+-----------+----------+-----------+
|Jones   |$54,334.15  |-$7,823.14 |05/01/2014|$25,313.12 |
+--------+------------+-----------+----------+-----------+
|Jones   |$54,334.15  |$308.00    |24/01/2014|$25,621.12 |
+--------+------------+-----------+----------+-----------+
|Pisa    |$86,423.81  |-$2,603.95 |17/01/2014|$83,819.86 |
+--------+------------+-----------+----------+-----------+
|Lee     |$115,378.89 |$22,019.49 |17/01/2014|$137,398.38|
+--------+------------+-----------+----------+-----------+

I am using Access 2010.

Upvotes: 0

Views: 78

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123799

For your sample data in a table named [activity]

Customer  Loan_Balance  SumOfAmount  Pstng_Date
--------  ------------  -----------  ----------
Macey             0.00     21545.59  2014-01-10
Jones         54334.15    -21197.89  2014-01-10
Jones         54334.15     -7823.14  2014-01-05
Jones         54334.15       308.00  2014-01-24
Pisa          86423.81     -2603.95  2014-01-17
Lee          115378.89     22019.49  2014-01-17

the query

SELECT
    t1.Customer,
    t1.Loan_Balance,
    t1.SumOfAmount,
    t1.Pstng_Date,
    t1.Loan_Balance + SUM(t2.SumOfAmount) AS Cum_Balance
FROM
    activity AS t1
    INNER JOIN
    activity AS t2
        ON t1.Customer = t2.Customer
            AND t1.Pstng_Date >= t2.Pstng_Date
GROUP BY
    t1.Customer,
    t1.Loan_Balance,
    t1.SumOfAmount,
    t1.Pstng_Date
ORDER BY
    t1.Customer,
    t1.Pstng_Date

returns

Customer  Loan_Balance  SumOfAmount  Pstng_Date  Cum_Balance
--------  ------------  -----------  ----------  -----------
Jones         54334.15     -7823.14  2014-01-05     46511.01
Jones         54334.15    -21197.89  2014-01-10     25313.12
Jones         54334.15       308.00  2014-01-24     25621.12
Lee          115378.89     22019.49  2014-01-17    137398.38
Macey             0.00     21545.59  2014-01-10     21545.59
Pisa          86423.81     -2603.95  2014-01-17     83819.86

Upvotes: 1

Related Questions