error in running total querry

[cliff]

Hi, I have small problem in performing running total. My data table
something like
union all loan disbursal and perioical repayments which indicates negative
value and loan disbursal amount positive value.

In short One loan disbursal entry for loan, whereas mutiple repayments on
same and different dates. when I do running total query,suppose there are
many repayments on same day it shows same running total for all (it reduces
on group of cdate rather than individual entries as shown in balance column).


my data is :-
lnno cdate repayments balance

5481 1/1/1999 405000 405000
5481 3/29/2001 -100000 305000
5481 3/29/2001 -50000 255000
5481 3/29/2001 -10000 245000
5481 3/29/2001 -10000 235000
5481 3/29/2001 -10000 225000
5481 4/29/2002 -25000 200000

5480 1/1/1999 500000 500000
5480 3/29/2002 -10000 490000
5480 3/29/2002 -10000 480000
5480 3/25/2003 -10000 470000
5480 3/25/2003 -10000 460000
5480 3/25/2003 -15000 445000

I tried with two different types of queries , but I am not result it above
format

SELECT Query59.lnno, Query59.cdate, Query59.repayments,
DSum("[repayments]","query59","[cdate]<=#" & Format([cdate],"mm/dd/yyyy") &
"#") AS balance
FROM Query59
ORDER BY query59.cdate;

SELECT a.lnno, a.cdate, a.repayments, sum(b.repayments) AS balance
FROM Query59 AS a INNER JOIN query59 AS b ON (a.lnno=b.lnno) AND
(a.cdate>=b.cdate)
GROUP BY a.lnno, a.cdate, a.repayments
ORDER BY a.lnno, a.cdate;


Please help solve this

thanks
cliff

 

[John Spencer]

Unless you have some way to distinguish between the two payments made on
the same loan on the same date there is no way (that I know of) to
handle this in a query. In a report you could use a control that
calculates a running sum of payments and use that to calculate a running
balance by subtracting the running sum of payments from the orginal balance.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================