Table Design Problem

[Joe Cilinceon]

Here is what I have:

Parent
Ledger table that contains the date of a payment, total payment taken,
payment period and a break down of what it pays. Now each item has a unique
transaction number and is linked to a tenant/unit by a LedgerID field.

Child
Payments table that is linked to the Ledger Table and holds the amount of a
payment, method of payment (1 for cash, 2 for check), tracking number (check
number). Now there can be several payment per a single transaction in that
someone may pay a payment with cash, check and credit card to equal total
due.

Additional
Now I'm looking for a method to reverse a payment such as a check bouncing.
At the moment I'm using a 3rd table called NSF that I copy the check/credit
card tracking number too, the date the payment was reversed and when it was
paid. I have to manually roll back the paid thru date and delete the break
down of the payment in the Ledger table now.

One other note here is I use a lookup table for the payment methods. Now
this table has 4 fields.
PaymentTypeID a number
PaymentGroup again a number but would have Visa and Master card in the same
group. Is used to simply group payment types together.
PaymentMethod a text field would have Cash, Check, Visa, Money Order etc.
AcceptedMethod is a True/False fields.

I was thinking perhaps I could use this table to flag a reversed payment.
Any help would be greatly appreciated.

 

[Jeff Boyce]

Joe

From your description, I'm wondering why you are trying to do this in
Access, rather than using an off-the-shelf accounting package?

Regards

Jeff Boyce
<Office/Access MVP>