Junction table question

[Beemerang]

Hi all

I am in the process of learning to make use of a junction table for many-to-many relationships and I think I'm starting to grasp it. My question is, once the tables and relationships have been set up, does the junction table get updated as you enter data into the fields on the two tables linked to the junction table or is the junction table simply a way to relate Table A and Table B via the junction table. So should I expect the junction table to remain empty as I enter records or should records be added to it as I populate the two data entry tables?

Beem

 

[Mr. B]

There will need to be a record in the junction table for each link you need between the two tables. The only way to have the relationship between the two tables is with a record in the junction table. You will also need to use the junction table when querying your data.

 

[Beemerang]

Many thanks for the reply!

How does the junction table then get populated? Do I have to manual create records or would it get populated through the data that I enter in the related tables?

 

[jdraw]

It does not get populated simply because you added record in the related tables.

To populate the junction table, I often have a form with two combo boxes and a button. The combos are populated with TableA data and TableB data. Once I select the related data values, I click the button.
On the Click event of the button I append a record to the junction table.

Good luck.

 

[Cronk]

I'll give you an example.

Say an organisation has a number of committees on which a number of staff are members. A different group of staff members are on the various committees.

Three tables, tblCommittee, tblStaff, and the many to many join table, tblCommStaff

You might have 2 forms, the first being Committees. It has a sub form, bound to the join table, showing members for that Committee record. The subform has a combo box with row source being the Staff member, but the bound to the StaffID field in the join table.

The second form would be for Staff members, with a sub form with a combo but this time showing the Committees the staff member is on.

Maybe someone else has a simple example to post.

 

[Beemerang]

It does not get populated simply because you added record in the related tables.

To populate the junction table, I often have a form with two combo boxes and a button. The combos are populated with TableA data and TableB data. Once I select the related data values, I click the button.
On the Click event of the button I append a record to the junction table.

Good luck.

Thanks for the response Jdraw!

 

[Beemerang]

I'll give you an example.

Say an organisation has a number of committees on which a number of staff are members. A different group of staff members are on the various committees.

Three tables, tblCommittee, tblStaff, and the many to many join table, tblCommStaff

You might have 2 forms, the first being Committees. It has a sub form, bound to the join table, showing members for that Committee record. The subform has a combo box with row source being the Staff member, but the bound to the StaffID field in the join table.

The second form would be for Staff members, with a sub form with a combo but this time showing the Committees the staff member is on.

Maybe someone else has a simple example to post.

Thanks Cronk, that helps a lot! I'm starting to "get" it now.