Payment Shcedule

davegauthier

New member
Local time
Today, 00:42
Joined
Dec 10, 2005
Messages
8
I am creating a db for a property management company. They want to be able to enter the monthly payment made each month on each property (there will be multiple properties involved) and also have a field that totals all payments made by the tenant over the course of their lease.

What might be the best structure for this type of situation?
Should I create 12 fields for a year of monthly payments for each record?
Should I create a seperate table for each property and it's payments?
Should I create an Excel spreadsheet for the payments and link that file to the db as a table?

Any help and suggestions on this are greatly appreciated. Thank you in advance.

Dave
 
Payments

Create a separate table for the payments which links back to the Properties Table. So for each property you have many payments. Make sure you have a unique ID for each Property (call it PropertyID) which is an autonumber. In the Payment Table have a unique PaymentID (autonumber Field) and have the PropertyID in this table set as a number field. Link the PropertyID field in Property Table to PropertyID in Payment field.

Hope this helps
 
davegauthier said:
I am creating a db for a property management company. They want to be able to enter the monthly payment made each month on each property (there will be multiple properties involved) and also have a field that totals all payments made by the tenant over the course of their lease.

What might be the best structure for this type of situation?
Should I create 12 fields for a year of monthly payments for each record?
Should I create a seperate table for each property and it's payments?
Should I create an Excel spreadsheet for the payments and link that file to the db as a table?

Any help and suggestions on this are greatly appreciated. Thank you in advance.

Dave

None of the above. the first two would violate normalization rules. The last is unnecessary. You add a table:

tblPayments
PaymentID (primary key autonumber)
PropertyID (Foreign key)
PaymentDate
PaymentAmount

This is all you need. You do NOT need to store the total of poayments, that's a calculation and, as a general rule) calculated values are not stored. You can easily do a report grouped by property that sums the payments.
 
ScottGem said:
This is all you need. You do NOT need to store the total of poayments, that's a calculation and, as a general rule) calculated values are not stored. You can easily do a report grouped by property that sums the payments.

This certainly helps. I think he wants to store the calculated field to use in other calculations. Would it be just as easy to simply use all the payments individually for the other calculations as well?

Also, when using/entering this information on a form, on the given form there is only one property being viewed at a time. How would you suggest entering the new payments on the form and then showing a list of payments made on that form? I understand how the reports can be produced to show this information, but I am trying to produce results based on the request of the user.

Question: Can the values be entered into a listbox as part of that record? Can the values in the listbox be calculated?
 
davegauthier said:
This certainly helps. I think he wants to store the calculated field to use in other calculations. Would it be just as easy to simply use all the payments individually for the other calculations as well?

Also, when using/entering this information on a form, on the given form there is only one property being viewed at a time. How would you suggest entering the new payments on the form and then showing a list of payments made on that form? I understand how the reports can be produced to show this information, but I am trying to produce results based on the request of the user.

Question: Can the values be entered into a listbox as part of that record? Can the values in the listbox be calculated?

Yes, anyplace you need to use the total of payments you would use the same expression to calculate it. Whether its just for display on a form or report or used in a further calc. In fact, using it in a further calc makes it more imperative not to store it. if you store it you have to make sure you update it before using it further. If you always recalculate based on base data you are getting the most up to date and accurate info.

You use a subform to enter payments. The subform will be linked to the payment table on PropertyID, so only payments on that property are shown.
 

Users who are viewing this thread

Back
Top Bottom