Relationships take work...

[Daryl Mhoon via AccessMonster.com]

What to do when relating payments with charges..? One charge to many
payments, one payment to many charges. Ultimately a many to many
relationship is ideal but difficult to deal with and I don't think it's
necessary.

I labor at tasks ranging from high voltage electrical to framing when I'm not
typing, and it’s obvious by my posts and the response to my posts that I'm
not succinct and I don't always know how to express myself. Sorry, no more
soliloquies.

I have three important tables in this situation. tblLeases, tblPayments,
tblCharges. There is a one to many relationship from tblLeases to
tblPayments and tblCharges. There is NO direct relationship between
tblPayments and tblCharges! Instead I decided to use forms, subforms and
code to allocate a payment or partial payment to any charge or charges.

Two tables are necessary. I do not see the need for a third intermediary
table to create a many to many relationship. I cannot see any problems with
my design. Has this been done before? Am I causing myself a future headache?


ANY SUGGESTIONS, COMMENTS OR HARRASMENT IS WELCOME,

DM

 

[Steve Schapel]

DM,

It depends on what you want the information to do for you. If all you
need is to determine that for Lease XYZ there are total unpaid charges
of $x, then your existing model will work just fine. If, on the other
hand, you need to determine that for Lease XYZ, Charge A has been paid
in full, Charge B has been partly paid, and Charge C is unpaid, then you
will need to revise the table design.