Query Criteria and DSUM? 1

[Cinfysue]

Hi, I need assistance with the following Microsoft Access query, please:

SELECT tblLoans.LoanID as ID, tblLoans.LastName as LastN, tblLoans.ProjDate, tblLoans.ProjAmt, [Enter Current Balance] AS OrigBal
FROM tblLoans;

I'd like to get a running total of ProjAmt column that takes the OrigBal (entered from user as criteria) and subtracts the first ProjAmt from that and then starts the running total in subsequent records.

ID LastN ProjDate ProjAmt Run Total
1 Smith 11/01/04 75000 OrigBal - ProjAmt(=R1)
2 Jones 11/05/04 80000 R1 - ProjAmt(=R2)
3 Limon 11/25/04 90000 R2 - ProjAmt(=R3)

and so on...


I've tried DSUM ("ProjectedWireAmt", "tblLoans") in the above query and am getting the overall running total and incorrect results.

Let me know what else I can provide and thank you very much!

 

[PHV]

Replace this:
DSUM ("ProjectedWireAmt", "tblLoans")
By this:
DSUM("ProjectedWireAmt", "tblLoans", "LoanID<=" & LoanID)

It is assumed that LoanID is in chronological order.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cinfysue]

Works Perfectly! Thanks so much!