Many to Many

[jc3k]

I'm setting up a many-to-many relationship between an instructors
table and a class table. 1 Instructor can have many classes. 1 Class
can be held by more then one instructor. I'm using a bridging table
between the 2.

I placed a 3rd table between them that consists of (ID, Instructor_ID,
Class_ID). The Instructor_ID and Class_ID were set to allow
duplicates. These were then linked back to the Instructor and Class
table.

In the form for the class, you may now select an instructor from a
combo box and click on add. The 3rd table is then updated with the
instructors ID and the Class ID. You may do this for multiple
instructors.

The problem I am having is that if you select an instructor, and then
hit add twice, they will be added to the class twice.

Is there a way of setting up some form of protection in the table
itself without having to resort to VBA to scan the table before you
add someting? Can you make the combination of field 2 and field 3 a
unique key?

I'm a bit stumped, as I don't do a lot of work in access and have only
taught myself what I needed to know at the time. So I don't have the
experience to know if this is a simple problem that I've never had to
deal with before.

Thanks.

Craig.

[Ben Eaton]

2 possible answers:

1. Get rid of the ID column in the bridging table and then turn the other
two columns into a joint primary key (do this in table design by pasting
over both fields and then right click and select primary key)

2. Create a new query, go into SQL View (View, SQL View), then paste the
following code in and run the query:

ALTER TABLE tbBridging ADD CONSTRAINT UQ_Instructor_IDClass_ID UNIQUE
(Instructor_ID, Class_ID)

(Remember to replace tbBridging with the name of your bridging table)

Either of these will throw a trappable error if a duplicate is entered.
Hope at least one is useful.

~Ben

"jc3k" <sp***@hotmail.com> wrote in message
news:13**************************@posting.google.c om...

I'm setting up a many-to-many relationship between an instructors
table and a class table. 1 Instructor can have many classes. 1 Class
can be held by more then one instructor. I'm using a bridging table
between the 2.

I placed a 3rd table between them that consists of (ID, Instructor_ID,
Class_ID). The Instructor_ID and Class_ID were set to allow
duplicates. These were then linked back to the Instructor and Class
table.

In the form for the class, you may now select an instructor from a
combo box and click on add. The 3rd table is then updated with the
instructors ID and the Class ID. You may do this for multiple
instructors.

The problem I am having is that if you select an instructor, and then
hit add twice, they will be added to the class twice.

Is there a way of setting up some form of protection in the table
itself without having to resort to VBA to scan the table before you
add someting? Can you make the combination of field 2 and field 3 a
unique key?

I'm a bit stumped, as I don't do a lot of work in access and have only
taught myself what I needed to know at the time. So I don't have the
experience to know if this is a simple problem that I've never had to
deal with before.

Thanks.

Craig.

[jc3k]

"Ben Eaton" <be************@hotmail.com> wrote in message news:<bn***********@news.wplus.net>...

2 possible answers:

1. Get rid of the ID column in the bridging table and then turn the other
two columns into a joint primary key (do this in table design by pasting
over both fields and then right click and select primary key)

Yep. This did it. To work in with some other code already there I
had to leave the ID in there. But I removed it as a primary key. I
didn't know about the joint primary key thing. I feel silly :)

Craig.

[Fred]

Hi.

Another option would be to set up a multifield unique index on the 2 keys of
the middle table.

While in design mode on the middle table, click on top bar View, Indexes...

A 'Indexes' pop up window displays the indexes.

Enter a new index name in the first column. on the same row in the second
column, select key from left table.
On the next row in the second column, select key from right table.
At bottom of the window select 'Yes' for unique.
That's it!


"jc3k" <sp***@hotmail.com> wrote in message
news:13**************************@posting.google.c om...

I'm setting up a many-to-many relationship between an instructors
table and a class table. 1 Instructor can have many classes. 1 Class
can be held by more then one instructor. I'm using a bridging table
between the 2.

I placed a 3rd table between them that consists of (ID, Instructor_ID,
Class_ID). The Instructor_ID and Class_ID were set to allow
duplicates. These were then linked back to the Instructor and Class
table.

In the form for the class, you may now select an instructor from a
combo box and click on add. The 3rd table is then updated with the
instructors ID and the Class ID. You may do this for multiple
instructors.

The problem I am having is that if you select an instructor, and then
hit add twice, they will be added to the class twice.

Is there a way of setting up some form of protection in the table
itself without having to resort to VBA to scan the table before you
add someting? Can you make the combination of field 2 and field 3 a
unique key?

I'm a bit stumped, as I don't do a lot of work in access and have only
taught myself what I needed to know at the time. So I don't have the
experience to know if this is a simple problem that I've never had to
deal with before.

Thanks.

Craig.