Making a Many to Many table (help a newbie)

[hellfire45]

So I am working on making an Access database at work but I don't really know how to do what I'm trying to do.



We have a Many to Many relationship here. Allow me to explain. We want to make a database to keep track of projects. A single project on the "project table" can be associated with multiple project managers on the "project manager" table. At the same time, a single "Project Manager" can be working on multiple projects on the "project table". I don't really know the "best practices" way to make this work. Can anybody shed any light on the two tables below so that I can get started on the right foot creating these tables and relationships? Thank you so much!

If I pull back a project, I'll get 3 or 4 people which creates multiple rows of the same project name etc... If I pull back all the people's names I get multiple rows also. Do I need another ID for the project managers and then a 3rd table to bring all the IDs together? And if so, does this just create a table with every possible combination or people and projects? Is there a better way to do this?

"Project" table

PID#	Project Name	Description	Start Date
1123	Backroom cleanup	Cleaning up the back room	1/1/2015
1135	Car repair	Fixing up the broken car	1/15/2012
1165	Textbook sale	Selling textbooks	2/2/2015
1168	Car Purchase	Purchasing a car	4/4/2012

"Project manager" table

PID#	Name	Supervisor	Type	Title
1123	Fred	Eric	PA	Analyst
1135	Fred	Eric	PA	Analyst
1165	Fred	Eric	PA	Analyst
1165	Steven	Eric	DA	Data Analyst
1168	Steven	Eric	DA	Data Analyst
1135	Jimmy	Bob	Systems	Systems Analyst
1168	Jimmy	Bob	Systems	Systems Analyst
1123	Steven	Eric	DA	Data Analyst
1135	Phil	bob	PA	Analyst

[pbaldy]

Typically you'd have a junction table. It would have a record for each combination, and have fields for the project ID and manager ID. You could use a form/subform to work with it. The form would be bound to either the project or manager tables, the subform to the junction table. Master/child links would restrict the subform to the project or manager being displayed on the main form.

[hellfire45]

Thank you for the advice!

May I please ask a followup question?


There are potentially multiple different groups of people involved in the projects. We have three different types of analysts at the firm and there could be any different number of when involved on a given project.

Does a junction table only work best when there are simply 2 groups of people involved? What if there were 4 total groups? Wouldn't there be like a huge number of results in the junction table query?

[pbaldy]

No followup questions!!

I'm not sure that changes anything, other than perhaps add a field to the junction table to denote the role the person has in the project. I would assume the analysts are all in the same table anyway.

[hellfire45]

Oh I see. Under that method, I would have the analysts all stacked up in the same column and not in unique columns. Like below?

PROJECT NAME Name Position
Project 1 Jim Systems
Project 1 Bob data
Project 1 Eric process
Project 2 Sarah Systems
Project 2 Susan data
Project 2 Rony process
Project 3 Greg Systems
Project 3 Peter data
Project 3 Anthony process

[pbaldy]

Yes. You wouldn't want to put them in fields (columns) because then your whole design has to be changed when the powers-that-be decide to add a new analyst.