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.

 

[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]

 

[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]

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.

 

[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/

 

[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/

 

[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

 

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

 

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

 

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

 

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

 

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