Enter Data into a Junction Table

[darkwan75]

This might be a ridiculously simple question but here goes anyway. I understand HOW to address Many to Many relationships in Access. I don't have a problem creating a third table with the one to many relationships to both parent tables. This I get. What I'm struggling with is how to enter data INTO the tables. I'm not sure if the junction table merely acts as a tool to join the other tables or if when I (or anyone else for that matter) enters data into which tables they have to enter data.



Do users have to enter data into all three tables? or does entering data into the to parent tables update the third (junction) table automatically? This is really causing some confusion for me when I'm designing forms because I'm not sure where data is to be entered. Is the junction table used at all in data entry? Do you need to enter duplicate data from both of the parent tables AND the junction table? Any clarification would be extremely helpful.

Thank you!

[jzwp11]

Normally, you would select one of the two parent tables on which to build your main form. The junction table would be bound to a subform on that main form and you would populate the key field related to the second parent table (the one not used in the main form) via a combo box whose row source is the second parent table.

Which parent is used for the main form depends on your workflow. For example, let's say that you are assigning training courses (one parent) to an employee (the other parent). Your list of courses stays relatively constant but you regularly need to add courses to an employees required training (junction). In this case, you can use a simple form to populate all of the courses. You would create a form/subform with the employee table bound to the main form and the junction table would be bound to the subform. In the subform, you would use a combo box based on the course table.


There are ways, to use the Not In List Event of the combo box in the subform to add new courses to the course table, but that will take some Visual Basic for Application code to achieve.

[darkwan75]

Thank you so much! that really does answer my question. I guess I was making it more difficult than it actually is. Thanks for your quick response!

[jzwp11]

You're welcome.