Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    Unanswered: Preventing duplicate record in table

    Is there a way to prevent a worker from entering duplicate records based on two fields in a table.
    Example:
    NAME FIELD REPORT HOURS SICK
    James Smith Weekly summary 12 2

    If a worker tries to enter this record again (BY MISTAKE) the system would look at the NAME FIELD AND THE REPORT field and determine that this was a duplicate record and not accept it and give an error message.

    Please explain in simple terms.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Alter table tableA
    add unique(NAME, REPORT)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, robert, this is microsoft access, you need to use access DDL

    create unique index namereportindex
    on timesheetstable ( name asc, report asc )

    see Common DDL SQL for the Microsoft Access Database Engine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi Rudy,

    I provided the DDL statement that does not explicitly name the constraint.

    In access 2003 the following statement is valid:

    alter table tableA
    add [constraint UC_ab] unique (name, name2)
    Last edited by r123456; 07-08-04 at 01:33.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, okay, that explains why i got a syntax error on your ddl in access 97
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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