Ways to avoid making an all to all relationships

[Braindead90]

I am creating a create a table that will have list people doing jobs. So the table will list jobs and the people associated with it. The problem is that many people can work one job and one jobs could be completed by many people.

I was told that would require an all to all relationship, the problem is that to have that can of relationship, I would need to list all possible combinations in a spearate table. I would do this but the table will updated by other people and that would mean I would have to redo that permutation table EVERY TIME. Is there a way around it?

 

[Mihail]

Is there a way around it?

No. It isn't something "around".
Access has a "native" solution for this very frequent situations.
It is so called "bridge table".

So, you have:

tblPeoples
ID_People - AutoNumber (PK)
PeopleName
.....

tblJobs
ID_Job - AutoNumber (PK)
JobName
....

and you need to design the "bridge table"

tblPeoplesJobs
ID_PeopleJob - AutoNumber (PK)
ID_People - Number (FK on tblPeoples)
ID_Job - Number (FK on tblJobs)
.....

PK = Primary Key
FK = Foreign Key (or Family Key)

 

[Braindead90]

No. It isn't something "around".
Access has a "native" solution for this very frequent situations.
It is so called "bridge table".

So, you have:

tblPeoples
ID_People - AutoNumber (PK)
PeopleName
.....

tblJobs
ID_Job - AutoNumber (PK)
JobName
....

and you need to design the "bridge table"

tblPeoplesJobs
ID_PeopleJob - AutoNumber (PK)
ID_People - Number (FK on tblPeoples)
ID_Job - Number (FK on tblJobs)
.....

PK = Primary Key
FK = Foreign Key (or Family Key)

So from this I am making two "one to all" relationships in this bridge table? Now if I make a query of this to display to the users who will be adding new info, would I be linking the bridge table to the query? I created the bridge table with the relationship but I dont undestand how will I be able to get my outcome from this...

 

[David R]

In any given situation, you're either going to be looking for the many Jobs that one Person has, or the many People who have one Job.

Those will be two different OUTER JOIN queries, each using all three tables but with an arrow going from the one-to-many relationship as needed.

 

[jdraw]

Bridge table, often called a Junction Table. Junction table used to resolve a Many to Many situation.
Here's a video that may help
http://www.youtube.com/watch?v=7XstSSyG8fw