distributing paid amount to specific months

  • Thread starter Thread starter ai_enjoi via AccessMonster.com
  • Start date Start date
A

ai_enjoi via AccessMonster.com

i have a table tblFundAccruals w/ field names IndexNum, PID, Month, DueAmount.
.. these amounts will be accrued every month plus the unpaid amount in
arrears during the past years... my problem is when a certain PID goes to pay
an amount for example 500, how can i possibly allocate this amount to his
monthly dues..

example, if his dues look like this:
IndexNum PID Month Due
1 23 in arrears 200
2 23 Jan 100
3 23 Feb 100

how can i distribute the 500 to the 3 dues, those in arrears, for jan and feb
and wud result with an amount of 100 as unapplied payment w/c will be used
for the next billing?? i really cant put my acts togther on this...any help
on this wud be appreciated ...
thank you
 
A running sum through the date (group on PID) would tell you the history of
the account:

PID date amount Running
23 2006.12.31 -200 -200
23 2007.01.01 -100 -300
23 2007.02.01 -100 -400
23 2007.02.05 500 +100
.....


Note that I dated the 'past dues' as somewhere in the past (ie. to simulate
the opening of the new year books, kind of).


Only the first three columns are data, the fourth one is computed, as given
by the query:


SELECT a.pid, a.date, SUM(a.amount), SUM(b.amount)
FROM books AS a INNER JOIN books AS b
ON (a.pid = b.pid AND a.date >= b.date)
GROUP BY a.pid, a.date


assuming your table name is books.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top