Allocating Payments to Charges

[Daryl Mhoon via AccessMonster.com]

I read where Rick advised against reinventing the wheel. I know there are
inexpensive accounting packages that would perform better than anything that
I could design with Access. I've taken a few accounting classes and I'm
aware of this fact.

Unfortunately they just don't suffice in my business (Property Management) so
I developed a game plan, set accounting asside for the moment and here is
what I have come up with. Tell me what you think...

I definately need two tables one for charges and another for payments. That
seems to be the consensus here also.

I've thought long and hard about how to relate these tables. I've considered
a one to many relationship with Payments on one side and Charges on the many
side. This won't work for me. Tenants occasionally overpay five $5.00 or by
one month's rent, or they may make multiple payments on one charge. My only
solution in that case is a many to many relationship. I've had horrible luck
with many to many relationships, for this and other reasons I'd prefer to
avoid them.

Instead I have tblLeases that is the one side of relations to: tblTenants,
tblVehicles, tblRecurringCharges, tblDeposits, tblCharges tblPayments (and a
few more I've forgotten) on the many side. I have no direct relations
between tblCharges and tblPayments save for their common table tblLeases. Is
there a reason why this won't work? As I see it I shouldn't have any
problems (if I figure out how to allocate monies via VBA) printing receipts,
Tenant Ledgers, Notices, and just about anything else that I need.

In tblCharges I have about 10 fields, the most important of them are:
[ChargeID] [LeaseID] [Date] [ChgAmnt] [Description] [AmntPaid]. tblPayments
in conjunction with tblPaymentMethods is less complex with basically the same
fields: [PmntID] [LeaseID] [Date] [PmntAmnt] [Notes] ([PmntAmnt] is
actually NOT a field in tblPayments, it is a Sum of the tblPaymentMethods.)

The form frmPayments serves as the place to process and relate this data. My
plan was to have two subforms, subfrmPayments, subfrmUnpaidCharges on this
main form. I would enter the payement amount then allocate the total to
subfrmUnpaidCharges (this is proving a problem...) They way I see it I don't
really need a relationship between charges and payments as long as RI is
enforced in ALL the relationships starting at tblLeases and foreign fields
are required.

It wont be the first time I've spent hours on a form or project to find that
my design was all wrong, I was hoping to use you folks as a sounding board
before I shoot myself in the foot again.

Thanks,
Daryl

 

[Steve Schapel]

Daryl,

I didn't read this post until after I had replied to the other one.
Anyway, a few random comments below...

... I've had horrible luck
with many to many relationships, for this and other reasons I'd prefer to
avoid them.

I know this is not directly answering your question. But here's the
core concept here... in database design, you can't really "prefer to
avoid" anything. The data model you use is pretty much determined by
the real-life relationships between the actual information you are
working with, plus the consideration of the uses you need to put the
data to. Anyway, you might be interested in this...
http://accesstips.datamanagementsolutions.biz/many.mht

...
In tblCharges I have about 10 fields, the most important of them are:
[ChargeID] [LeaseID] [Date] [ChgAmnt] [Description] [AmntPaid].

Well, here I think is a mistake. If I understand you correctly, I don't
think there should be a [AmntPaid] field in the tblCharges table.
Either you want to track the payments against the charges, or you don't.
If you don't, then you are tracking the payments against the Lease,
and for this purpose the LeaseID field in the tblPayments table serves
the purpose. If you do, then you will need a way of relating the
payment to the charge. Either way, any piece of information should be
in one place only in your database, and in this case, the tblPayments
table is the correct place for the AmtPaid. Instead, you probably
should have a ChargeID field in the tblPayments table, so that every
payment is related to a charge. This should take care of every
eventuality. If you receive a single payment, which is supposed to be
credited against more than one charge, any reason why you can't split it
and enter a separate payment record for the amount related to each
charge? Mind you, this may duplicate the purpose of the
tblPaymentMethods table, which I haven't quite grasped yet what this is
all about.

 

[Daryl M via AccessMonster.com]

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

 

[Steve Schapel]

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?

 

[Daryl M via AccessMonster.com]

Does any of this make sense?

ABSOLUTELY!

I did this in one of my previous databases but I didn't realize the [Type]
field could be so diverse.

I have a query qryUnpiad where [ChargeAmnt] - [AmntPaid] > 0, that I use
quite a bit and that kinda perverted my view of the whole thing. If I were
to use one table, I suppose I could just a Balance query.

By the way, it is important for me to allocate a payment to a charge. As the
legal systems work here if I do not allocate payments to the late fees before
I allocate them to the charge I cannot collect them.

Transactions vs. Charges/Payments is a battle I thought I had won a long time
ago. It seems a waste to separate Charges and Payments when they share so
many common elements. I disassembled a few other databases I'd found and
most of them used separate tables. There are a few threads on this forum
dedicated to this question and consensus is two tables is the way to go.

You asked for an example of charges. At this point I have only one recurring
charge per tenant, Rent, but I can foresee a time in the future where Parking
or Storage for example might be necessary. For this reason I have
tblRecurring Charges. One-time charges such as Late Fees, Court Costs,
Damages, and Buss Passes can also be levied.

Thanks for the input Steve. Back to the drawing board for me. I'm sure
you'll hear from me again.

 

[Steve Schapel]

Daryl,

Thanks for the further discussion.

I am interested to know that you feel there is a consensus that costs
and payments should be in separate tables. To me, it depends on the
details of the project.

Anyway, you have mentioned that you do need to allocate payments to
charges. I would still recommend the same as I did before. You just
need another field in the table, I suppose you could call it ChargeID,
but it is in fact the TransactionID of the Charge that the Payment
relates to. So in practice in the table, whenever a Transaction is a
Charge, this field is always blank, but if the Transaction is a Payment,
the Charge has to be entered. You can enter this on your form via a
combobox whose Row Source is a query that lists all the unpaid or
part-paid Charges for the LeaseID that the Payment Transaction that you
are entering is related to. I have done this sort of system many times,
and it works very well.

--
Steve Schapel, Microsoft Access MVP

Does any of this make sense?

ABSOLUTELY!

I did this in one of my previous databases but I didn't realize the [Type]
field could be so diverse.

I have a query qryUnpiad where [ChargeAmnt] - [AmntPaid] > 0, that I use
quite a bit and that kinda perverted my view of the whole thing. If I were
to use one table, I suppose I could just a Balance query.

By the way, it is important for me to allocate a payment to a charge. As the
legal systems work here if I do not allocate payments to the late fees before
I allocate them to the charge I cannot collect them.

Transactions vs. Charges/Payments is a battle I thought I had won a long time
ago. It seems a waste to separate Charges and Payments when they share so
many common elements. I disassembled a few other databases I'd found and
most of them used separate tables. There are a few threads on this forum
dedicated to this question and consensus is two tables is the way to go.

You asked for an example of charges. At this point I have only one recurring
charge per tenant, Rent, but I can foresee a time in the future where Parking
or Storage for example might be necessary. For this reason I have
tblRecurring Charges. One-time charges such as Late Fees, Court Costs,
Damages, and Buss Passes can also be levied.

Thanks for the input Steve. Back to the drawing board for me. I'm sure
you'll hear from me again.