select previous rows and calculate

I have two tables

1. Loan
2. Loan_Payments

in MS Access

Loan inc开发者_Python百科ludes

1. Ref_Loan
2. Amount_Loan

Loan_Payments includes

1. Ref_Loan
2. Amount_Paid
3. Date

Loan Table

Ref_Loan    Loan_Amount
loan 1      12000
loan 2      24000
loan 3      5000 

Loan_Payments

Ref_Loan Amount_Paid Date
loan 1     1000    01/01/2011
loan 1     1000    01/02/2011
loan 1     1000    01/03/2011
loan 2     1000    01/01/2011
loan 2     1000    01/02/2011

I want an out put that will show like this;

Ref_loan    Amount_Paid    Date      Balance
loan 1      1000         01/01/2011  11000
loan 1      1000         01/02/2011  10000
loan 1      1000         01/03/2011   9000
loan 2      1000         01/01/2011  23000
loan 2      1000         01/02/2011  22000
loan 3                                5000

---

Access query:

select L.Ref_Loan, P.Amount_Paid, P.Date,
    L.Loan_Amount - DSum("Amount_Paid", "Loan_Payment",
    "Ref_Loan=" & L.Ref_Loan & " AND Date<= #" & Format(P.Date, "yyyy/mm/dd") & "#")
from Loan AS L
left join Loan_Payments AS P on P.Ref_Loan = L.Ref_Loan
ORDER BY L.Ref_Loan, P.Date

However, if you are using Jet from C#, DSum may not work. In which case, write out the proper SQL in full

select L.Ref_Loan, P.Amount_Paid, P.Date,
    L.Loan_Amount - (
        SELECT SUM(P2.amount_paid)
        FROM Loan_Payment AS P2
        WHERE P2.Ref_Loan=L.Ref_Loan
        AND P2.Date <= P.Date)
from Loan AS L
left join Loan_Payments AS P on P.Ref_Loan = L.Ref_Loan
ORDER BY L.Ref_Loan, P.Date