How do I figure out amout due from transaction file

[Dennis]

Hi,

I have two tables. A Renter's table and a Renter's transactions table. The
Renter's table has the renter's name and lease information including the rent
amount. Each month I will run a Append query to create a rent due
transaction in the Rent Transaction table. Then I will have a form which
will allow the users to post payments to the Rent Transaction table. I also
have Late Charges, Deposit Transaction, Misc Chgs, and Adjustmetns in the
Rent transaction table.

I'm use to carrying the balance due on the Renter's table (in other
databases) however, from what I read that is not the Access way. So I am
trying to figure out how do do you calculate how is due (which I can do by
totaling ALL of the rent due and rent paid rows in the Rent Transaction
table) but how do you end up with a single amount to decide if it is past due?

I can see where if everything is due by the 10th of the month, you can just
charge a late fee then. But what do you do when the due dates on are
different days? I can see that you would select the transactions that are
due on a given day, but how would you select the payments that are associated
with that due date unless you posted that same due date on each payment
record. But then you have to have some process to assign the due date from
the rent due transation to the rent payment transactions.



Any suggestions?

Thanks,

I have a transaction file that contains rent due transactionis and rent paid
transactions with nothing

 

[KARL DEWEY]

Use one transaction for 'rent due' and 'payment' by having 2 date fields and
dollar field.

 

[Dennis]

Karl,

That works great as long as everyone pays the entire amount each time.
However, they do not do that.

If I have only one record / row for multiple payments, I either have to
create multiple fields for each payment, which violated database design
rules, or I loose track of the fact that multiple payments have been made on
different days.

 

[KARL DEWEY]

One way is to make the transaction a partial payment and create another with
same due date.