Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: Prevent double entries

    In my Appointment table, I want to prevent the same person from being entered twice for the same appointment. I went to Indexed (No Duplicates), which worked but, it does not allow the same member to have an appointment for the next day or any other day.

    Please can you tell me how I can correct this?

  2. #2
    Join Date
    Feb 2004
    Posts
    22
    If a person can have only one appointment per day, then create a compound index using the person's ID and the date of the appointment. Then set the unique property to Yes.

    Jack

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    If a person can have only one appointment per day, then create a compound index using the person's ID and the date of the appointment. Then set the unique property to Yes.

    Jack
    Thanks Jack

  4. #4
    Join Date
    Feb 2004
    Posts
    22
    Glad to help.

  5. #5
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    Glad to help.
    This does not work.

  6. #6
    Join Date
    Feb 2004
    Posts
    22
    Be more specific.

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    Be more specific.
    Sorry Jack, I was frustrated with the other problem I was having but thats been solved thanks to Rudy.

    Anyway, one member can only attend one class. So for example
    John Smith - 9:00am - Aerobics.
    John can not be entered for this class again. However he can be entered for a Boxercise class at 16:00.

    My question is: How can I prevent a member from being entered in the same class twice?

    When you say Unique property, do you mean Indexed (yes no duplicates)?

  8. #8
    Join Date
    Feb 2004
    Posts
    22
    Yes. It is the same thing.
    In table design under field properties, they use the latter.
    In the Indexes dialogue, they use the former.

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    Yes. It is the same thing.
    In table design under field properties, they use the latter.
    In the Indexes dialogue, they use the former.
    Jack, when I try this I get an error message saying,

    "The Changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redifine the index to permit duplicate entries and try again."

    What does the above mean?

  10. #10
    Join Date
    Feb 2004
    Posts
    22
    If you have defined the compound index, then this is a normal message you would get when you try to save a record that would create a duplicate field combination. At this point I do not know what you are using as a compound index. Is it :
    member - time
    member - class
    member - time - class

  11. #11
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    If you have defined the compound index, then this is a normal message you would get when you try to save a record that would create a duplicate field combination. At this point I do not know what you are using as a compound index. Is it :
    member - time
    member - class
    member - time - class
    I am using

    ClassID,
    ClassName
    Date.

    I have chosen all three to be my primary keys. When I go to Indexed (No Duplicates), that message comes up.

  12. #12
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by jackb22
    If you have defined the compound index, then this is a normal message you would get when you try to save a record that would create a duplicate field combination. At this point I do not know what you are using as a compound index. Is it :
    member - time
    member - class
    member - time - class
    I forgot to mention, my other field are
    MemberID
    Instructor ID

  13. #13
    Join Date
    Feb 2004
    Posts
    22
    Now it is getting complicated. You can only have one primary key in a table, whether it is simple or compounded. If you already have a simple one, say MemberID, then all you should do is set the unique property in the Indexes dialogue, WITHOUT it being a primary compound key.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the error msg means that you already have conflicting data in your table.

    find the records that have the same
    ClassID,
    ClassName
    Date
    (A's query wizard will set up a find duplicates query for you)

    fix the bad records

    and try setting no dupes again.

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by izyrider
    the error msg means that you already have conflicting data in your table.

    find the records that have the same
    ClassID,
    ClassName
    Date
    (A's query wizard will set up a find duplicates query for you)

    fix the bad records

    and try setting no dupes again.

    izy
    Thanks Izyrider and Jack. I have sorted out the data in other related tables. I have tried the help option on Access and it says:

    Create a multiple-field index using the fields you want to prohibit duplicate values for. Leave the Indexes window open when you have finished defining the index.

    This is still allowing me to enter duplicate data. How shall I go about entering a compound key?

Posting Permissions

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