Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unanswered: Checking for Duplicates

    I have an application where a few forms are using the same table. One of my fields if for Social Security Number and I have set that as my primary key because there cannot be duplicates of the SSN. My problem that the SSN is not filled in on every form and Access does not like this. Of course, with a primary key you cannot leave that blank in a record. So my question is, how do I check for duplicates without making my SSN a primary key? I'm fairly new to Access so this may seem like a pretty basic question but my application is just about complete and this is one thing holding me back. Any suggestions?

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    One option is to not make the SSN your primary key and create a hidden primary key that is just some random autunumbered field.

    You could put the SSN on each form but just set the visible property to false if it does not need to be seen.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by jtuck
    I have an application where a few forms are using the same table. One of my fields if for Social Security Number and I have set that as my primary key because there cannot be duplicates of the SSN. My problem that the SSN is not filled in on every form and Access does not like this. Of course, with a primary key you cannot leave that blank in a record. So my question is, how do I check for duplicates without making my SSN a primary key? I'm fairly new to Access so this may seem like a pretty basic question but my application is just about complete and this is one thing holding me back. Any suggestions?
    Hi jtuck,

    First things first, seems there is something going on that should be looked into. You say you have the SSN as the PrimaryKey?? Generally when you set a PrimaryKey, that field HAS to be filled in as that is what makes each record unique. So that field should be set to "Required"
    Second, IF you in fact have it set as a PrimaryKey whereas there can be no duplicates, what DUPLICATE are you actually trying to find? There is in Access what's called a Find Duplicates Query Wizard and you find that by going to your Toolbar at the top, Click on the NewObject icon, then Click on Query. You'll have a selection of queries with the Find Duplicates Query Wizard being one of them. It should walk you through the way pretty easily.
    I would look into how you Tables are set up though. A poorly designed table can result in lots of extra work as well as not being able to do all the things you should be able to do.

    just trying to offer a little help,
    have a nice one,
    BUD

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,
    Code:
    Select columnB, columnC, columnD
    from tableA ta
    where 1 <
      (select count(*)
       from tableA tav
       where tav.columnB = ta.columnB AND
                tav.columnC = ta.columnC AND
                tav.columnD = ta.columnD)
    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
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You need a primary key which is unique for every row, so that rules out the SSN if you cannot retrieve it from each form.
    So I would suggest you make the column containg the SSN a nullable uniquely indexed field. This caters for the situation where you don't know the SSN and stops the duplication problem. You can handle the error as gracefully as you prefer - either let JET / Access do it or write you own error handler to check if the error refers to a duplicate index

    I'd be very very cautious about using dlookups, they seem great but come at a high performance hit. Used very occasionally they can be very effective. for the SSN I'd be tempted to define the row in the table as null and with a unique index,then trap the error to see if a duplicate SSN was entered. Depending on yiur user base you could omit the error trap as the message is (almost) self explanitory

Posting Permissions

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