Duplicate student ID but not Course ID

G

ghost

I have 2 tables, one for student info and the other one is for Student's
courses. In the second table "Student's Courses" there are three fields,
Student Number, CourseID and Mark. The Student Number is foreign key and each
student has 10 courses. What I want to do is preventing the courseID from
duplicating, but the problem is there are too many students may take this
course. How to ensure that the student does not have duplicated course in
this table.
Thanks
 
K

KARL DEWEY

First check and see if you have duplicates now by using a totals query like
this ---
SELECT [6600Course].StudentNumber, [6600Course].CourseID,
Count([6600Course].CourseID) AS CountOfCourseID
FROM [6600Course]
GROUP BY [6600Course].StudentNumber, [6600Course].CourseID
HAVING (((Count([6600Course].CourseID))>1));

Correct any duplicates. Backup your database.

Open your Student courses table in design view. Click on menu VIEW -
Indexes. In the new window that opens select StudentNumber and CourseID.
Type in a name for the index. Click out of Index Name and back again.
Another window opens at the bottom of the current window. Select Unique -
Yes and leave Ignore Nulls - No.
Save.
 
Top