By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,214 Members | 1,516 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,214 IT Pros & Developers. It's quick & easy.

Relationship and Many-to-Many

P: 3
I am attempting to create some relationships in Access 2007. I would like some help confirming that it is being done in the correct way.

I would like Clients, Projects and Staff tables. Each client can have many projects with the company. Also, many projects has many staff working on it.

Firstly, does the diagram correctly illustrate this issue?


Secondly, have I correctly recreated this within Access?



Many thanks.
Attached Images
File Type: jpg relationship.jpg (19.6 KB, 2604 views)
File Type: jpg relationshipimage.jpg (12.9 KB, 687 views)
Jan 1 '12 #1

✓ answered by Stewart Ross

What you have implemented makes sense to me and would appear to resolve the m-m relationship between project and staff.

This is NOT what is shown in the other diagram, however - but the diagram does not make sense to me.

Firstly, the link table is shown as having a 1-m relationship with staff but it should be m-1 (for each entry in the project-staff there can be only one staff member that matches - but the diagram shows the crows foot (many relationship) on the wrong side).

Secondly, the link table is shown as carrying forward the client ID as a foreign key, but this makes no sense as client is shown as 1-m linked with project (for each project there can be only one client - in which case client can be found directly from the joined tables without carrying the client ID into the linking table).

-Stewart

Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
What you have implemented makes sense to me and would appear to resolve the m-m relationship between project and staff.

This is NOT what is shown in the other diagram, however - but the diagram does not make sense to me.

Firstly, the link table is shown as having a 1-m relationship with staff but it should be m-1 (for each entry in the project-staff there can be only one staff member that matches - but the diagram shows the crows foot (many relationship) on the wrong side).

Secondly, the link table is shown as carrying forward the client ID as a foreign key, but this makes no sense as client is shown as 1-m linked with project (for each project there can be only one client - in which case client can be found directly from the joined tables without carrying the client ID into the linking table).

-Stewart
Jan 2 '12 #2

P: 3
Thank you for your reply. Slightly confused which diagram you are referring to in each paragraph. My fault for not labelling them clearer.

From what I understand, I have attached the newly created basic diagram and the relationships within Access.

Is this what you were explaining?

Once again, thank you for the fast reply.


Attached Images
File Type: jpg basicdiagram.jpg (13.9 KB, 592 views)
File Type: jpg access.jpg (16.8 KB, 546 views)
Jan 2 '12 #3

Expert Mod 2.5K+
P: 2,545
Your basic diagram now accurately reflects the relationships that you've set up in Access, which in turn resolves the m-m between project and staff.

You've also taken out the unnecessary Client ID from the link table, so this now looks complete and consistent to me. Well done!

A characteristic of using a link table to resolve an m-m relationship is that it generates two 1-m relationships, where the 'many' part of the relationship is with the link table itself. If you compare your current basic diagram to the first one you will see that the crows-foot 'many' symbols in the current one are on the link table like this, but in your first diagram they were the wrong way round on the other tables.

-Stewart
Jan 2 '12 #4

P: 3
Thank you very much, it does make a lot more sense now.
Jan 2 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.