 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2004many to many relationship
I have created a database and it wasnt until we started entering data that I realised I had a many to many relationship between 2 tables - selection table and employeedetails table. (I know this is bad planning but can I fix it without disturbing my database too much?) I have created a join table with only the 2 primary fields in it from the existing tables ie selectionID and EmployeeID. I have set the primary key to include both these primary key fields from the other tables. However, this creates a one to one relationship between both the existing tables and the join table. My aim is to have one table with all the employee details entered only once. Each employee may have many selection records. How do I achieve this? Lynn Create a new primary key (Autonumber), and remove the multiple-field primary key. You may also need to click on/delete the relationships, then re-establish them.  SignatureGood luck Jeff Boyce <Access MVP OK I have done this and have managed to get 1 to many relationships as follows: Employee details linked to join table using the selection ID. The selection table linked to the join table using the employeeID. But now I have a non updateable query based on this relationship. Any ideas? >-----Original Message----- >Lynn > >Create a new primary key (Autonumber), and remove the multiple-field primary >key. You may also need to click on/delete the relationships, then >re-establish them. I'm not sure I understand. What you describe is the proper way to fix a many-to-many relationship. That it results in a one-to-one means that you haven't implemented it properly. I am envisioning something like this: Selection Linking EmployeeDetails ======== ========= ===========SelectionID(pk)------< SelectionID (cpk) |---EmployeeID (pk) ...(other fields) EmployeeID (cpk)>--| ...other fields This would be the proper way to fix it and should result in two one-to-many relationships that are updateable. Now, to implement this in a form, I usually use one of the two tables in a Main form and a JOIN of the other two in a subform. On my website, see sig below, is a small sample database called "ImplementingM2MRelationship.mdb", which illustrates how this is done. Signature--Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org > I have created a database and it wasnt until we started > entering data that I realised I had a many to many [quoted text clipped - 13 lines] > > How do I achieve this? When I link as you suggest below with selection ID in the selection table to selection id in the link table and employeeID in the employee table to employee id in the employee table, they both appear as one to one relationships. What do I do to implement this properly? I will have a look at your example to see if I can work out what I am doing wrong, but at the moment, I cannot see it. >-----Original Message----- >I'm not sure I understand. What you describe is the proper way to fix a many-to-many relationship. That it results in a one-to-one means that you haven't implemented it properly. I am envisioning something like this: >Selection Linking EmployeeDetails >======== ========= ============ >SelectionID(pk)------< SelectionID (cpk) |---EmployeeID (pk) >....(other fields) EmployeeID (cpk)>-- [quoted text clipped - 3 lines] > >Now, to implement this in a form, I usually use one of the two tables in a Main form and a JOIN of the other two in a subform. On my website, see sig below, is a small sample database called "ImplementingM2MRelationship.mdb", which illustrates how this is done. $a001280a@phx.gbl... >> I have created a database and it wasnt until we started >> entering data that I realised I had a many to many [quoted text clipped - 13 lines] >> >> How do I achieve this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.