Let's say you have table Transactions with columns TransactionID (autonumber primary key), TransactionDate, StudentID, FeeDue, FeePaid a query to return the cumulative balances and arrears would be like this:
SELECT T1.StudentID, T1.TransactionDate, T1.FeeDue, T1.FeePaid,
SUM(T2.FeeDue-T2.FeePaid) – (T1.Feedue -T1.FeePaid) AS Arrears,
SUM(T2.FeeDue-T2.FeePaid) AS Balance
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate)
AND (T2.StudentID = T1.StudentID)
GROUP BY T1.TransactionDate, T1.FeeDue,T1.FeePaid, T1.TransactionID, T1.StudentID
ORDER BY T1.StudentID, T1.TransactionDate,T1.TransactionID;
Ken Sheridan, Stafford, England