many to many relationship

[lynn atkinson]

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?

 

[Jeff Boyce]

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.

 

[lynn atkinson]

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?

 

[Roger Carlson]

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.

 

[Jeff Boyce]

Lynn

(see Roger's response)

 

[Guest]

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)>-- | ...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.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


"lynn atkinson" <[email protected]>

wrote in message [email protected]...