Confused with table relationships (1 Viewer)

[plasteredric]

Hi Guys, well im a bit further down the line setting up a property database and i cant figure out how to set up a suitable relationship between 2 tables :banghead:.
Table 1 is for leases, which can have up to 4 tenants (each has seperate field)
Table 2 is a table for each tenant

now I need to relate the two, each lease can have multiple tenants at the same time and each tenant can have multiple leases over several years.

Does anyone have any suggestions?

 

[Beetle]

Yes. You have a many-to-many relationship so you need three tables. One for the Lease, one for the Tenants, plus a junction table to manage the relationship. Example;

tblTenants
TenantID
FirstName
LastName
*other fields for details specific to each tenant

tblLeases
LeaseID
StartDate
EndDate
*other fields for details specific to each Lease

tblTenantLeases (the junction table)
TenantID
LeaseID
*other possible fields that are specific to the relationship

 

[plasteredric]

ah, I see.
In my lease table I currently have the fields:
Tenant1
Tenant2
Tenant3
Tenant4

Does using a junction table means I dont need the multiple tenant fileds?

Also, when it comes to imputting the data, will i need to put it into both the normal table and junction table? or is there are way of auto-updating one from the other.

 

[Beetle]

In my lease table I currently have the fields:
Tenant1
Tenant2
Tenant3
Tenant4

Does using a junction table means I dont need the multiple tenant fileds?

Yes. The junction table is the proper way to handle this, not repeating fields in the Lease table.

Also, when it comes to imputting the data, will i need to put it into both the normal table and junction table? or is there are way of auto-updating one from the other.

Keep in mind that data entry is done at the Form level, not the Table level, so you would create a main form with a sub form, where the main form would be based on either the Lease table or the Tenant table - depending on what type of data display/entry was needed - and the sub form would be based on the junction table. See my posts in this thread for a bit more on this and a small example db.