Re: Allocating Payments to Charges

Tech-Archive recommends: Fix windows errors by optimizing your registry

Daryl,

As intimated in my earlier reply, I strongly advise against the use of "ApplyAmount" code to the AmntPaid field in the tblPayments table. In fact, very strongly. In fact, I strongly advise against having this field at all. In fact, go right now to your database and delete the AmntPaid field from the table. Otherwise, I promise that it will ultimately cause a blowout in your paracetomol budget.

I don't know what is really involved with the Charges. Maybe a couple examples would help. Is it stuff like Rent, Bond, Power? So, this is how I would understand it at the moment... For example, every 2 weeks a tenant incurs a Rent charge. So, on 1st July, there is a rent charge of $100 levied, and on 15 July there is a rent charge of $100 levied. On 2 July, they give you a money order for $80 and cash of $20. On 15 July they give you a cheque for $50. So, the payments on 2 July were most likely in payment for the 1 July rent, and the 15 July payment is most likely for the 15 July rent. But really, does it matter? Isn't all you want to know that they incurred charges of $200 and they have paid $150 and they still owe $50? If I was your tenant, and I knew you were tracking payments like this, I would give you some money and tell you it was for my September rent, and I'll pay the July rent later <g>

To be honest, on the basis of what you have told us so far, you only need one table. Call it Transactions. Fields like this...
TransactionID
LeaseID
TransactionDate
Amount
Type
Description

The example I gave above would result in 5 records in this table, and on the basis of which you will be able to derive whatever information you need.

I would use a numerical code for the entries in the Type field. For example, -1 = rent charge, -2 = power, 1 = cheque payment, 2 = cash payment, etc. You can still have 2 separate forms for charges and payments if you like, if it makes it easier to understand, using the example I gave the Record Source of the Payments form would be:
SELECT * FROM Transactions WHERE Type > 0

Does any of this make sense?

--
Steve Schapel, Microsoft Access MVP

Daryl M via AccessMonster.com wrote: 
Thanks Steve,

I understand.  I'm so glad I have somebody to talk to on this.  Your
absolutely right, in designing this app I can't let my ability (or lack there
of) or dislikes to interfere with the process.  If it has to be it has to be.

I've created many to many relationships before, I know how to add the
intermediate table and create the relationship.  My problems arrise later
when I'm fighting with the code and forms.

Maybe I don't have the foresight but I can't think of a time when I would
need to know when which pmnt is associated with which charge, save for the
moment when I'm actually processing the payment.  At which point I would use
a txtbox and code to "Apply Amount" to the AmntPaid field.  When I print a
ledger I can run a query and organize the pmnts and charges by date, or can I.
.?  Goes against what I learned in accounting, but as Rick mentioned
accounting is best left to commercial software available at Walmart.

As far as tblPaymentMethod...  A tenant will pay their rent in very strange
ways.  Most of the time it is with a check or money order for the exact
amount.  I have one tenant who knows he can't save money.  He gets paid twice
a month; with his second paycheck he purchases a money order for anywhere
from 50 to 150 bucks. He signs over his first paycheck and may or may not
cover the balance with cash and coin.  I have some girls from France that
paid multiple months with travelers checks.  I have a few students who pay
with a check from their parents.  I have roomates that pay with two checks.
I have one guy who purchases a money order for the maximum amount of $500.00
and pays the rest in cash.  If I can add notes about each different method
namely the checks, if they bounce reversing the payment is much simpler.

What do  you think?  Should I stick with manual labor..?

DM


.

Relevant Pages

  • Re: A Builders Terms & Conditions
    ... The Customer accepts liability for the safe custody of materials ... collectors fees incurred in obtaining the said payment. ... 8.Interest Charges On Overdue Accounts ...
    (uk.legal)
  • A Builders Terms & Conditions
    ... The Customer accepts liability for the safe custody of materials ... collectors fees incurred in obtaining the said payment. ... 8.Interest Charges On Overdue Accounts ...
    (uk.legal)
  • Re: I resign!
    ... I usually end up with no payment in May as they being the account back to zero due. ... I get an email on my phone bill when it has been drafted and I can check on it. ... I have just had to change my credit card to new numbers because of some unauthorized payment to iTunes and where it came from,,,, nobody knows and the only way to stop it was kill the card. ... or at least most persistent problem we have is charges to our telephone bill for services we didn't authorize or order. ...
    (alt.support.arthritis)
  • Re: BT & DD
    ... I just got an electricity bill with a reading at ... A VERY large majority of those using DD to pay their utility ... They have a perfect right to vary the charges according to the ... payment method used. ...
    (uk.legal)
  • Re: Table design question for exist program
    ... Ok I would also have the transaction number in that table as well. ... need away to tie a particular rent to a given rent payment. ... > ledger transactions associated with this particular table would be ... >>> have all the different types of charges. ...
    (microsoft.public.access.tablesdbdesign)