Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Orlando, FL
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent dup records

    I am creating a DB that keeps track of students and classes. I have 3 tables: Students, Classes, Class Detail. Students -> Many-Many -> Class Detail. Class -> M-M -> Class Detail. I have created a main form from Class table and a subform from a Student->ClassDetail query. The subform is where an existing student is entered into an existing class. How do I prevent duplicate Students from being entered into one class. It doesn't seem to matter how I index the ClassDetail table I can't seem to tell it not to enter a student into the same class twice.

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prevent dup records

    I think you mean that Class is 1->M to Class Detail and Students is 1->M to Class Detail, which effectively makes Students M<->M to Class. The usual way would be to create a multi-field unique index in Class Detail, so that any combination of student and class is indexed no duplicates.
    Charlotte

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Orlando, FL
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent dup records

    Charlotte, you are correct in that my Students->ClassDetail relationship is a 1-many and the same thing with the Class->Class Detail. I mistated it. If I understand you correctly, I believe I did what you said but it still doesn't work. The only fields the ClassDetail have are a key field, ClassID and StudentID which are linked to the corresponding tables. I have set the Indexed property of the link fields in ClassDetail to No Duplicates and Duplicates OK and neither way works. Is this what you are talking about when you say a multi-field unique index?

    Thank you for your help.

    Larry

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent dup records

    You want to create an index composed of the StudentID field and the ClassId field. Open the table in design mode. Open the Indexes dialogue box. Go to the first blank row and enter an index name in the first column. In the field column select one of the fields mentioned above. In the Index Properties change the Unique setting to Yes. Go to the next row, leave the name field blank and select the other field mentioned above in the field column. You should now have a unique index composed of the two fields mentioned above. Save the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •