Monthly account balance

[Frank Sung]

Hi,

How to write a SQL query to get account's monthly balances?

A table looks like below.

Account Date Trans. Int. Rate
======= ======== ======== ========
0001 01/01/04 $100 3%
0001 02/01/04 $0 2%
0001 03/01/04 $50 1%
0001 04/01/04 -$100 2%
0001 05/01/04 $0 1%
0002 01/01/04 $200 3%
0002 02/01/04 -$50 2%
0002 03/01/04 $0 1%
0002 04/01/04 $50 2%
0002 05/01/04 $0 1%

The query's result should look like below.

Account Date Beg Bal Trans. End Balance
======= ======== ======== ======== ========
0001 01/01/04 $0.00 $100.00 $103.00 =(0+100)*(1+3%)
0001 02/01/04 $103.00 $0.00 $105.06 =(103+0)*(1+2%)
0001 03/01/04 $105.06 $50.00 $156.61 =(105.06+50)*(1+1%)
0001 04/01/04 $156.61 -$100.00 $57.74 =(156.61-100)*(1+2%)
0001 05/01/04 $57.74 $0.00 $58.32 =(57.74+0)*(1+1%)
0002 01/01/04 $0.00 $200.00 $206.00 =(0+200)*(1+3%)
0002 02/01/04 $206.00 -$50.00 $159.12 =(206-50)*(1+2%)
0002 03/01/04 $159.12 $0.00 $160.71 =(159.12+0)*(1+1%)
0002 04/01/04 $160.71 $50.00 $214.92 =(160.71+50)*(1+2%)
0002 05/01/04 $214.92 $0.00 $217.07 =(214.92+0)*(1+1%)

Beginning balance is last month's ending balance.
Ending balance = (Beginning balance + Transaction) * (1 + Interest Rate)

Thank you in advance.

Frank

 

[Jeff Boyce]

Frank

What you've described sounds a lot like what Excel "does for a living". Is
there a business need to keep this in Access, or could you extract the data
to Excel and process there.

 

[Frank Sung]

Jeff,

Thank you for your quick response.

Yes, it is working like Excel. However, our data are stored in SQL
server database. I think it may not be a good way to use Excel and
VBA to run the data.

I have no problem to write a running total query.

key value
=== =====
1 10
2 5
3 10
4 20
5 30

SELECT t1.key, t1.value, SUM(t2.value)
FROM t AS t1, t AS t2
WHERE t1.key >= t2.key
GROUP BY t1.key, t1.value

key value running sum
=== ===== ===========
1 10 10
2 5 15
3 10 25
4 20 45
5 30 75

I know how to write a cumulative product query.

key value running multiply
=== ===== ===========
1 10 10
2 5 50 (=10*5)
3 10 500 (=10*5*10)
4 20 10000 (=10*5*10*20)
5 30 300000 (=10*5*10*20*30)

SELECT t1.key, t1.value, EXP(SUM(LOG([t2.value])))
FROM t AS t1, t AS t2
WHERE t1.key >= t2.key
GROUP BY t1.key, t1.value

(values are not 0 or negative)

I know I can use stored procedures to make it work. If it is
possible, I am looking for such queries to get my monthly account
balanaces.

Thanks,

Frank