child tables

[short]

I recently was put in charge of a database someone else did, and since then
has left.
I'm confused as to why this person did this. There is one table that is
basically the main table or parent table and then there is a child table with
a PK for the child and the a FK of the parent. But then this person took both
PK and placed them into a seperate table. All this table has is the PK for
both tables.

Now I'm trying to on a form update the child table's information, and when I
try to do this it does not fully update the information on the form. The info
is in the table but when I'm trying to look up different parts of tables with
the combo box the information is never there for the child table. I put in
the PK's for both into this thrid table and then it works, but I do not want
to keep having to do this, both tables should automaticaly do this.

Any ideas on what is going on?

 

[Arvin Meyer [MVP]]

If you check the relationships in the Tools menu, you will probably find
that the 3rd table is linked to the other 2, by those PKs.

What you are seeing is a many to many relationship. One child can have
multiple parents, and one parent can have multiple children.

 

[Allen Browne]

A one-to-many relation would use 2 tables:
- main (parent) table, with PK.
- related (child) table, with its own PK and FK to main.

A many-to-many relation would use 3 tables:
- table1 with a PK.
- table2 with a PK.
- table3 with its own PK, FK to table1's PK, and FK to table2's PK.

If you have the 3rd table, and table2 also has an FK to table1's PK, it
would suggest that either:
a) someone stuffed up (thinking it was a one-to-many, when it was actually a
many-to-many) and did not clean up after themselves, or
b) there are multiple relations between the tables to be traced here.

 

[short]

Thank you, this has helped out so much!!

A one-to-many relation would use 2 tables:
- main (parent) table, with PK.
- related (child) table, with its own PK and FK to main.

A many-to-many relation would use 3 tables:
- table1 with a PK.
- table2 with a PK.
- table3 with its own PK, FK to table1's PK, and FK to table2's PK.

If you have the 3rd table, and table2 also has an FK to table1's PK, it
would suggest that either:
a) someone stuffed up (thinking it was a one-to-many, when it was actually a
many-to-many) and did not clean up after themselves, or
b) there are multiple relations between the tables to be traced here.