Deducting totals in priority order

[Guest]

I have a design issue that I hope someone can give some input. Perhaps this
can be solve with some type of query but I haven't an idea how.

Let say some one owes a debt of $2000. Each payment he pays back goes back
to multiple debtors who have different priority to the debt. For example,
the first debtor is entitled to $1200, the 2nd $300, the 3rd $500. So when
we receive a payment, we want to see this:
P1 P2 P3 Bal
--------------------------------------
Orig $1200 $300 $500 $2000
pymt -500 -500
--------------------------------------
700 300 500 1500
pymt -700 -200 -900
--------------------------------------
0 100 500 600

Can this be done for an on screen display in real time in some sort of
"grid"? I am not talking about a printed report. None of the solutions I
came up with is dynamic. Does anyone have a suggestion? Thank you.

 

[Guest]

yes - if there is a fixed hierarchy one can create VB code that will
implement the pay-off in the manner you need.

there is nothing intrinsic in Access in terms of its regular feature
selections - one must write a VB module and call it

If you are not comfortable w/ VB then it won't be easy to explain via this
forum - you might look for assistance with this - or possibly someone will
donate the code and talk you thru it....

 

[Guest]

Thank you for the input. I am comfortable writing code in VBA. In fact, the
solution I am thinking of would require coding, but again, is not very
"dynamic".

Right now the payment transactions are stored in a table, call it tblPymt.
What I am thinking is this:
- Have a table call tblDebtDetail, which would list the original balances
for P1,P2,P3,..(the number of Ps is fixed so that wouldn't be too much of an
issue)

- Have a table call tblPymtDetail, which would have fields called P1,P2,P3,
etc

- Each time we get a payment, I would query the running balances for
P1,P2,P3 and add a record in tblPymtDetail that breaks down how the payment
is applied to P1,P2, etc. So in my example, I would insert 2 records in
tblPymtDetail with -500 for P1 for the first record and -700 for P1 and -200
for P2 in the second record.

- I can then sum P1, P2, etc in tblPymtDetail to get how much is applied to
each

Anyone have a better suggestion? The problem I see with this solution is
that if I ever have to reverse a payment, I will have to reprocess all the
subsequent payment details because the running totals would have been
different.
Any other potential problems anyone can see?
Thanks.

 

[Guest]

with a hierarchy you could If/Then your way from P1 to P2 to P3
etc....applying the calculation as appropriate to each.

off the cuff ; keep in mind that possibly a reversal could be handled by
doing an add +$amount rather than the normal subtraction i.e. an offset of
the exact amount...but this could be tricky if other payments have already
been processed....

of course more than one way to skin a cat