Form-Subform and another Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I am using Access 2000 and I need someone to help me create the
following:
I want to link 3 tables in "Forms" in the following manner as an example:
Every Condo (one table with Condo addresses) can have many owners (one table
with many owners linked to the addresses table by One to Many relationship)
and every owner may have many renters (a seperate table with renters linked
to the owners table).
I know how to create a Form and a Subform but how can I get the second
Subform to point to the first Subform on the same master form?
Addresses
Owners Info
Renters Info

Your help is greatly appreciated,

Iram/mcp
 
There is many-to-many relationship between Condo and Owner, i.e. one owner
can have a share in many condos, and one condo can have many owners.
Likewise, over time, one Renter can rent many condo, and one condo can have
many renters. The many-to-many means you need a junction table to break it
down into a pair of one-to-many relations.

Here is the most basic structure to manage what you describe:

Condo table:
CondoID primary key
CondoAddress ...

Owner table:
OwnerID primary key
Surname ...

Renter table:
RenterID primary key
Surname ...

CondoOwner table:
CondoOwnerID primary key
CondoID relates to Condo.CondoID
OwnerID relates to Owner.OwnerID
Share percentage of ownership this person has in this condo.
StartDate date this person took a share in this condo
EndDate date this person disposed of their share of this condo.

CondoRenter:
CondoRenterID primary key
CondoID relates to Condo.CondoID
RenterID relates to Renter.RenterID
StartDate date this person began renting this condo
EndDate date this person stopped rentingthis condo.

The EndDate fields are blank if the entries are current.

If the junction table idea is new, here's a basic explanation and example:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
 
Back
Top