Best Forms to Design

[Joshann]

I have some tables that are set up as follows (these are not the actual names of the tables and fields, I'm using generic names so that I don't get a bunch of questions about the setup to begin with; I believe they are set up in the best possible way):

Table1 has a one to many relationship with Table2
Table1 has a one to many relationship with Table3
Table2 has a many to many relationship with Table3

Fields for Table1
Table1_ID
Field1Table1

Fields for Table2
Table2_ID
Table1_ID
Field1Table2

Fields for Table3
Table3_ID
Table1_ID
Field1Table3
Field2Table3
Field3Table3

Fields for Table4
Table2_ID
Table1_ID

Can anyone offer some advice on the best way to set up some forms for data entry with these tables? I think having Table1 be the recordsource for a parent form, having Table2 be the recordsource for one subform, and having Table3 be the recordsource of another subform would work for the one to many relationship between Table1 and Table2 and the one to many relationship between Table 1 and Table3. But I can't figure out how to then include the many to many relationship between Table2 and Table3.

 

[Joshann]

They really are related as follows:

1->2
1->3
Yes, these are two indpendent relationships. But there is another relationship: 2<->3. This relationship is independent of 1.

 

[Joshann]

Here's an example:

Table1 contains waypoints.
Table2 contains facilities.
Table3 contains companies.

Each waypoint can have more than one facility, but each facility can have only one waypoint. They have a one to many relationship.

Each waypoint can have more than one company, but each company can have only one waypoint. They have a one to many relationship.

Each facility can have more than one company, and each company can have more than one facility. They have a many to many relationship.

So can anyone give some advice as to the best way to set these up in forms?

 

[Joshann]

I see what you're saying except that the relationship between 1 and 3 really is independent of the relationship between 1 and 2, and the relationship between 2 and 3 really is independent of the other two relationships. The data is actually as follows:

fac1 - way1
fac2 - way1
fac3 - way1
fac4 - way2
fac5 - way2
fac6 - way2

comp1 - way3
comp2 - way4
comp3 - way5
comp4 - way5

fac1 - comp2
fac2 - comp1
fac3 - comp3
fac3 - comp4
fac4 - comp1
fac5 - comp4

 

[Joshann]

I will try that. Thank you very much!