Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detecting duplicate records (Access 2000)

    A friend is creating an Access database to track contest winners at her radio station. She has a table Winners in which the disk jockeys will enter personal data about the winners, using SSN last-4 as the primary key. She has created a form Winners with all fields from the table, for the disk jockeys to enter the data. A business rule is that the station manager wants to avoid letting a previous winner win again within 30 days.

    My thought is that when the DJ enters the last-4 for a winner and tabs to the next field in the form, an appropriate event handler (BeforeUpdate?) needs to check for an existing record with that same last-4. If it doesn't, the data entry would continue. If it does, the current record would be deleted (canceled? or whatever to avoid an error message) and the existing record would be loaded into the form. If the existing record has a WinDate less than 30 days old, a message box would pop up informing the DJ that the caller is ineligible. Otherwise, WinDate would be updated to the current date.

    Am I on the right track, or is there an easier/better way? Thanks in advance for any assistance.

    Duane

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting duplicate records (Access 2000)

    I think you are on right track, but a few thoughts...
    Will the last 4 numbers of the SSN give you a proper primary key; that is can 2 people have the same last 4 numbers?
    After the DJ enters the key on the form, it should be the On Exit event (when they leave that field) that triggers the search for the existence of that record. Then, if it exists, you could display a message, cancel the entry and display the existing record.
    Your date logic seems correct; if <=30 the caller is ineligible. Else update the field to the system date.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Detecting duplicate records (Access 2000)

    Thomas is correct in questioning whether the last 4 digits of a SSN are unique. In fact, in a large metro area, the probably approaches 1 that a given person will match the last 4 of someone else. You would be better of to store the whole thing as an identity check, and also match on last name. That gets you a pretty good key - you could raise a warning when somebody has the same last 4, and if they have the same last name - make it a stronger message.
    Wendell

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting duplicate records (Access 2000)

    Thanks for the responses. You're both right, of course, about the lack of uniqueness of the SSN last-4. I'll suggest to her that she either go with full SSN (they may not want to do that for privacy reasons) or else use a composite key of last-4 plus last name to (almost) guarantee uniqueness. Thanks also for pointing out the correct event to use for the test.

    Duane

Posting Permissions

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