composite key for many to many relationship

[What's in a name]

I am trying to set a many to many relationship using a join table. I can see
in help that a composite primary key is used.
I can't find how to do this.

 

[Ken Snell MVP]

Tell us the details about your parent tables and the junction table, and
what the "key" fields would be for the junction (not join) table.

 

[John W. Vinson]

On Fri, 15 May 2009 17:18:01 -0700, What's in a name <What's in a

I am trying to set a many to many relationship using a join table. I can see
in help that a composite primary key is used.
I can't find how to do this.

It's not strictly essential to do so but it's usually a good idea. To create a
composite primary key in the table design window, ctrl-click both fields (up
to ten fields actually!) and click the Key icon.

In a join table these two fields will usually be the two foreign key links to
the two "one" side tables in the relationship

..--

John W. Vinson [MVP]

 

[What''s in a name]

Thanks for tip. Actually control click does not allow me to select more than
one field. I thought this was really strange, but might resolve if I close
and open computer again. However in the top left corner I can select the
whole table and that does permit me to assign two fields to primary key.

 

[What''s in a name]

OK.
I have a students table and a courses table and trying to create many to
many relationship using junction [thanks for correction] table. I have used
CourseID and StudentId as PK in respective tables and trying to create
composite key using these two fields as foreign keys in Enrollments table
(the junction table). I just am having difficulty selecting two fields
simultaneously which thus prevents me from creating the composite key in the
junction table.

 

[Ken Snell MVP]

It appears that you were able to resolve this issue (per your reply to John
Vinson)? Normally, you should be able to click on one field, then hold the
Ctrl key down and click on another field in that table, then drag the
clicked fields to the second table to make the join.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

OK.
I have a students table and a courses table and trying to create many to
many relationship using junction [thanks for correction] table. I have
used
CourseID and StudentId as PK in respective tables and trying to create
composite key using these two fields as foreign keys in Enrollments table
(the junction table). I just am having difficulty selecting two fields
simultaneously which thus prevents me from creating the composite key in
the
junction table.

Tell us the details about your parent tables and the junction table, and
what the "key" fields would be for the junction (not join) table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[KenSheridan via AccessMonster.com]

You need to make sure you click on the field selectors; those are the little
grey squares to the left of the field names in design view. When the cursor
is over the field selector it changes to a right-pointing arrow. You should
then be able to select multiple fields by Ctrl-Clicking. As in your case the
two fields are on adjacent lines you can also simply click on the field
selector of one and then drag down over the other without the need to hold
down the Ctrl key.

Ken Sheridan
Stafford, England

Thanks for tip. Actually control click does not allow me to select more than
one field. I thought this was really strange, but might resolve if I close
and open computer again. However in the top left corner I can select the
whole table and that does permit me to assign two fields to primary key.

It's not strictly essential to do so but it's usually a good idea. To create a
composite primary key in the table design window, ctrl-click both fields (up

[quoted text clipped - 6 lines]

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

I have a students table and a courses table and trying to create many to
many relationship using junction [thanks for correction] table. I have used
CourseID and StudentId as PK in respective tables and trying to create
composite key using these two fields as foreign keys in Enrollments table
(the junction table). I just am having difficulty selecting two fields
simultaneously which thus prevents me from creating the composite key in the
junction table.

What happens if a student flunks the course and takes the course a
second time?

Tony

 

[What''s in a name]

Yep. That's the one. SOOOO simple when you know how. Thanks a heap. It may
not be politically correct but I think it sucks that the access help button
does not provide any explanation of this. I really appreciate the time taken
by this group to answer my question and am disappointed that access help is
so poor.

 

[Fred]

I may be missing something....the experts answered your question as posed,
but are you sure that you don't have somethign mixed up?

I would have thought that your joins would be simply 2 single field links to
two FK fields in the junction table:

Students.StudentID (PK) to Enrollments.StudentID(FK)

Courses.CourseID(PK) to Enrollments.CourseID (FK)

 

[Douglas J. Steele]

Yes, the joins would be as you outline, but if you want to ensure that you
can't have the same student take the same course more than once, you need to
create a primary key on the Enrollments table.

 

[KenSheridan via AccessMonster.com]

And even if they can take the same course more than once there is going to be
at least one candidate key, e.g. StudentID, CourseID and EnrolmentDate.

Ken Sheridan
Stafford, England

Yes, the joins would be as you outline, but if you want to ensure that you
can't have the same student take the same course more than once, you need to
create a primary key on the Enrollments table.

I may be missing something....the experts answered your question as posed,
but are you sure that you don't have somethign mixed up?

[quoted text clipped - 6 lines]

Courses.CourseID(PK) to Enrollments.CourseID (FK)

 

[Fred]

In our case, duplicates of the two linking fields in the junction table are
common and a part of the plan. One example is where the "type" of
relationship is a field in the junction table, and two entities can have
several types of relationships. Another is BOM / manufacturing DB's (M-M
between assemblies and the parts used to build them) when a junction table
record is an "instance of use" where there is other info related to that
intance of use. ( e.g. an IC might be used twice on a circuit board, once
as "IC1" and once as "IC2".)

So I never thought of situations where the junction table needs to enforce
rules as you describe.

 

[KenSheridan via AccessMonster.com]

There's no problem with duplication of the join columns. In my example
StudentID and CourseID are the foreign keys referencing the primary keys of
Students and Courses and can (as a pair) legitimately be duplicated. The
point I was making is that where the two are not a candidate key there will
generally be another column which, taken with the two forms a candidate key.

Ken Sheridan
Stafford, England