-1

I'm new to Access and trying to understand m:m relationships. I understand how to create the junction table containing a composite primary key. What I'm having trouble with is what to do next.
Tbl1 has tbl1PK, Tbl2 has tbl2PK, and JunctionTbl1_2 has J1PK and J2PK. How do I populate JunctionTbl1_2?
If I want to do a query on the records in Tbl1 and Tbl2, do I actually do the query on the junction table?
I'm just a little lost on how to use the table. Any help would be appreciated.

Les Programmer
  • 121
  • 2
  • 15
  • Can you post sample data? your question is unclear. Without sample data, and knowing exactly what you are trying to achieve there's not a whole lot this community can do for you. You seem to have at least two separate questions. They should separate posts. Be as specific as possible on your problem, gives examples and exactly what point in the process you are having difficulty. – Gene Sep 03 '15 at 11:21
  • @gene Sorry. I am designing an inventory and warehouse management application. I have 50 employees that can work in 10 different warehouse locations producing over 200 items. So, the products come from many employees and from various locations. I need to track who does what and where they did it. I have 3 tables (to start with). i)Employee: E_IDPK, E_Name ii) Products: P_IDPK, P_Name iii) Employee_Products: E_IDPK, P_IDPK. What data/records need to go in the junction table? How do I do a query to tell which employee did what and which product came from which employee? – Les Programmer Sep 04 '15 at 13:56

1 Answers1

0

This sounds a lot like this question I answered recently, Multiple Many-to-Many Relationships (Circular Relationship) See if that answer gives you enough information for what should go in the tables. Once you have the junction (more technically the many-to-many tables) populated, a query to see which products were created by a specific employee would look something like this:

select p_idpk from product_employee as pe where pe.e_idpk = e.e_idpk

Let us know if you need more direction.

Community
  • 1
  • 1
Lynne Davidson
  • 183
  • 1
  • 1
  • 14
  • Thanks. Excuse the simplicity of my question but ... I have data in the Employee and the Product tables (imported). Now, when setting up the junction table do I need to, as your example shows, manually enter all the possible combinations of Employee and Product? Is there a faster way? When I add a new Employee will I need to add a new record in the Employee table _and_ the various combinations in the Product_Employee table? I'm obviously a little fuzzy on exactly how to use the junction table. I do understand your query example, but I'm not clear on how the data is properly set up. – Les Programmer Sep 05 '15 at 23:36
  • The best way to enter the table data would be to populate the individual tables (employee, location, product) and then set up an insert query joining two of the tables and put in the two keys into a many-to-many linking table. If it's not straight forward to join employee to product you may need to enter the data manually. When you add a new employee, you could grab his/her employee id and do an insert query like – Lynne Davidson Sep 06 '15 at 07:50
  • Sorry, the previous comment timed out on me. The best way to enter the table data would be to populate the individual tables (employee, location, product) and then set up an insert query joining two of the tables and put in the two keys into a many-to-many linking table. If it's not straight forward to join employee to product you may need to enter the data manually. When you add a new employee, you could grab his/her employee id and do an insert query like 'iNSERT INTO Employee_Product (e_idpk, p_idpk) VALUES (1,3), (1,5),(2,7),(2,5)' – Lynne Davidson Sep 06 '15 at 08:07
  • Looks like I'm going to have to fill in the table manually. I'll try to create a table in Excel and maybe use a VBA loop to move the data to the junction table or import the Excel table to the junction table. – Les Programmer Sep 06 '15 at 15:28