473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Relationship and Many-to-Many

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, 4080 views)
File Type: jpg relationshipimage.jpg (12.9 KB, 812 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

4 3342
Stewart Ross
2,545 Expert Mod 2GB
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
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, 700 views)
File Type: jpg access.jpg (16.8 KB, 674 views)
Jan 2 '12 #3
Stewart Ross
2,545 Expert Mod 2GB
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
Thank you very much, it does make a lot more sense now.
Jan 2 '12 #5

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

Similar topics

1
by: huzhenghui37 | last post by:
what relationship between this mail list and the python group on Google? i saw the same info on both
2
by: nfr | last post by:
I understand an assembly is nothing more than a collection of types in a versioned binary (EXE or DLL) in one or more physical files. Is there any kind of relationship between the assembly name and...
0
by: guy | last post by:
Hi, How can i create a relationship between 2 tables? TIA
5
by: Marek Kotowski | last post by:
I mean: which RDBMS 1) allows to declare that relationship is in fact 1-1, and 2) protests when one tries to violate the 1-1 relationship, that is inserts into a table two records with the same...
1
by: timmy_dale12 | last post by:
Hi Im a sql newbie , and have created two tables with a foreign key relationship. How do i insert into these tables. If i insert into the primary table will the foreign key field in the second...
0
by: toy | last post by:
For a weak relationship, will 0 and 1 in the ER diagram represent partial and total participations of the 2 entities sets respectively or vice versa.
2
by: Bill Short | last post by:
The following code copies recordset rs into recordet rst and adds it to tblVMSParts. The records being copied are the data that shows on a subform. I copy all of the data from the main record,...
43
by: Zeng | last post by:
It's so messy w/o the "friend" relationship. Does anyone know why it was not supported in C#. It's almost about as bad as it doesn't support the inheritance hierarchy and method reference...
3
by: Ricky | last post by:
Hi Can anyone explain what are the relationship between COM , ActiveX, DLL ? I really get confused about the terms... Ricky
3
by: Randy | last post by:
Hello, I've got a Windows client app which hits a web service. The web service works fine unless I'm outside the firewall using a VPN connection. I know my VPN connection is working fine because I...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.