Hi,

i am very new to access and trying to design a fairly simple database which will store payment information for my rental properties. 

I have created four tables:

Tenants

Properties

Leases

Payments

The tenants table will contain all my tenant information, first name, last name etc.

The properties table will contain property info, address, number of rooms etc

The leases table will contain details of the leases associated back to the tenant and property tables - it contains fields for (Lookup to the tenant name, lookup to the property address, First payment date, payment schedule (monthly, weekly etc) and and default payment amount. 

The payments table is where i will record all payment information - this is the table im struggling with a little. 

I need to be able to enter payments against leases, and when i select a lease from a combo box in the payment table which looks back to the lease id in the lease table, i want a a field which stores the default pay amount to automatically update based on the information in the lease table. 

Whats the best way for me to achieve this.

Thanks

You need to put in a foreign key into your setup in order to do this since nothing in the lease and payment queries will be the same.

okay - but how can i get it to fill in the standard payment amount in the payment table using the value from the lease table when a tenant is selected from a combo box in the payments table?