I’m making a query for an installment payment history which I named PaymentHistoryQ with the following fields:
OrderID from OrderTbl
TotalDue from OrderTbl
PaymentID from PaymentTbl
DatePaid from PaymentTbl
AmtPaid from PaymentTbl
TotalPaid which is a DSUM of AmtPaid per OrderID
Balance: [TotalDue] - [TotalPaid]
As I am creating a payment history table, I intend to make a ‘PrevBalance’ field which will serve as a beginning balance. The first record must be equal to the amount in ‘TotalDue’ while the succeeding records must copy the amount from ‘Balance’.
I have tried using this query but there seems to be a problem.
PrevBalance: (SELECT TOP 1 Dupe.Balance FROM PaymentHistoryQ AS Dupe WHERE Dupe.OrderID = OrderTbl.OrderID AND Dupe.DatePaid < PaymentTbl.DatePaid ORDER BY Dupe.DatePaid DESC)
I would really appreciate your help.