How to create a junction table?

[rbrule]

Hello,

I have two existing tables with related data. Neither table has unique field that can be used as a Primary key field with a corresponding field in the other table. I need to relate these two tables so I can extract certain data from each, in one query.

How do I create a "Junction table". Both the above tables have a primary key created with auto number. I know the "junction table" needs to be made of primary keys from the host tables. When I create the "junction table" how do I populate it with the data from the other two tables and how do I create the junction?

 

[joeselch]

Let's say you have a Fruit Salad database...
tblSalads and tblFruits
with a number of Salads that can be created by combining any number of Fruits, with each Fruit available to be used in any number of Salads

Your junction table would be:
tblFruitSalads
consisting of three fields

• fldFruitSaladID (unique ID for this table)
• fldSaladID (from tblSalads)
• fldFruitID (from tblFruits)

To create the junction, use the Relationships window to join the junction table to the other two tables:
tblSalads one-to-many with tblFruitSalads
tblFruits one-to-many with tblFruitSalads
enforcing referential integrity, of course.

To populate the junction table in the course of "normal" data entry, you would use a form with a sub-form.
In this case, we'd base our main form on tblSalads and our sub-form on tblFruitSalads.
The sub-form is linked to the main form by fldSaladID.
To make data entry (or, fruit picking ;-) easier, we could use a combo box based on a query of tblFruits to display the names of the fruits.

In your case, it sounds as though you have existing data in both tables that needs to be matched up in the junction table. So, presumably, you already know how the records in one match up with the records in the other?
If so, you can use append queries to populate the junction table.

 

[Pat Hartman]

Download my many-to-many sample db from the Sample databases section.

 

[rbrule]

Download my many-to-many sample db from the Sample databases section.

There are 11 threads in the sample databases section and none are entitled many to many sample db. Can you send it to me?

 

[Pat Hartman]

It isn't showing because you have a filter set that only shows threads newer than a given date. The search feature works pretty well.
Many-to-Many

 

[rbrule]

It isn't showing because you have a filter set that only shows threads newer than a given date. The search feature works pretty well.
Many-to-Many

Thank you very much.

rbrule

 

[rbrule]

Let's say you have a Fruit Salad database...
tblSalads and tblFruits
with a number of Salads that can be created by combining any number of Fruits, with each Fruit available to be used in any number of Salads

Your junction table would be:
tblFruitSalads
consisting of three fields

• fldFruitSaladID (unique ID for this table)
• fldSaladID (from tblSalads)
• fldFruitID (from tblFruits)

To create the junction, use the Relationships window to join the junction table to the other two tables:
tblSalads one-to-many with tblFruitSalads
tblFruits one-to-many with tblFruitSalads
enforcing referential integrity, of course.

To populate the junction table in the course of "normal" data entry, you would use a form with a sub-form.
In this case, we'd base our main form on tblSalads and our sub-form on tblFruitSalads.
The sub-form is linked to the main form by fldSaladID.
To make data entry (or, fruit picking ;-) easier, we could use a combo box based on a query of tblFruits to display the names of the fruits.

In your case, it sounds as though you have existing data in both tables that needs to be matched up in the junction table. So, presumably, you already know how the records in one match up with the records in the other?
If so, you can use append queries to populate the junction table.

Thank you for your help.

rbrule