Many-to-many relationships

[Geezer]

Hi all, would appreciate some pointers with a table relationship I'm trying to build in Access 2003

Currently have 2 tables:

1) Indoor_Units consisting of numerous fields:

Product_ID (primary key)
Spec fields (height, weight, etc)
Others (price, optional accessories, etc)

​

2) Outdoor_Units similar to the above but without "Others" fields

Each table contains unique data but there can potentially be several outdoor units for one indoor unit and likewise numerous indoor units can be paired with one specific outdoor unit.

It would seem the best way around this is to create a junction table consisting of ID pairs from each of the outdoor and indoor tables, correct?

How should I be handling the data in a form, via subforms? How do I ensure that if for example I add a totally new outdoor unit that this record ID is updated to the junction table I'm assuming this will need to be manually done?

Thanks and appreciate your input, any pointers would be great.

Gareth

 

[milehighfreak]

Hi all, would appreciate some pointers with a table relationship I'm trying to build in Access 2003

Currently have 2 tables:

1) Indoor_Units consisting of numerous fields:

Product_ID (primary key)
Spec fields (height, weight, etc)
Others (price, optional accessories, etc)

​

2) Outdoor_Units similar to the above but without "Others" fields

Each table contains unique data but there can potentially be several outdoor units for one indoor unit and likewise numerous indoor units can be paired with one specific outdoor unit.

It would seem the best way around this is to create a junction table consisting of ID pairs from each of the outdoor and indoor tables, correct?

How should I be handling the data in a form, via subforms? How do I ensure that if for example I add a totally new outdoor unit that this record ID is updated to the junction table I'm assuming this will need to be manually done?

Thanks and appreciate your input, any pointers would be great.

Gareth

You're correct, make a link table with its own primary key, and two fields, each data table's record IDs. To automatically update the link table, create a query with the data fields from each data table and the ID fields from the link table. Then create a form and include all fields from the query; lock and disable the two ID fields, they will be automatically generated when you start entering data in the subform.

Edit: Alternately, add the ID field from the subform's table instead of the link table, and use that in the subform creation to enter that with a combobox, to avoid duplicates.

 

[Geezer]

You're correct, make a link table with its own primary key, and two fields, each data table's record IDs. To automatically update the link table, create a query with the data fields from each data table and the ID fields from the link table. Then create a form and include all fields from the query; lock and disable the two ID fields, they will be automatically generated when you start entering data in the subform.

Edit: Alternately, add the ID field from the subform's table instead of the link table, and use that in the subform creation to enter that with a combobox, to avoid duplicates.

Thanks milehigh,

All working fine except when I try to add a totally new record for the subform (junction table) I get the message that I cannot add or change a record because a related record is required in table Outdoor_Units.

My Indoor_Unit table is linked to the main form while the junction table is sitting in my subform. Adding a new Indoor_Unit record works great and I can add an Outdoor_Unit from the combo box in the subform but only if it already exists in the Outdoor_Unit table.

Thinking I might have to add a control to create a new Outdoor_Unit such as a button which opens a "New Outdoor_Unit" form where the user can input the details. This then should update the junction table combo box for the Outdoor_Unit.

Is there perhaps an easier way, maybe I've missed something?

Thanks again.

Gareth

 

[milehighfreak]

You shouldn't be directly editing the junction table at all. Edit whichever you make the sub-form from within the table, and as long as the junction fields are present, they will autofill.

 

[Geezer]

Thanks again milehigh.

Think I have it sorted. Created main form using Indoor_Unit table and then the subform combining the Junction and Outdoor_Unit tables into a query. Then linked the main and subforms using the Indoor_Unit ID in the main form with the corresponding ID in the subfrom from the Junction table.

Appreciate your assistance.

Gareth