By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Many to Many

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
"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.
Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.