Check for Many Duplicate SSN's

[Randy Blake via AccessMonster.com]

I have an import button that imports data from excel to my access database
which is viewable in a form. My form is in datasheet view. There can be as
many as 900 records. Once the new data is in the form, I need to check the
SSN field against the exisitng table for duplicates and then list those
records that are duplicates. If no duplicates, then append those records to
the table.

Does anyone know how I can code this function??

Thanks,
Randy

 

[Roger Carlson]

Well, if you simply create a Unique Index on the SSN field and write an
Append query, the duplicates will be automatically rejected. However, you
need to know that under rare circumstances, legitmate duplicates of Social
Security numbers can exist.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Randy Blake via AccessMonster.com]

yes, but I'm importing new records into an exisitng table. How do I code to
list the duplicates found before running the append query?

I'm thinking I might need a popup form to list any duplicates found so that
they can be deleted first and then run the append query,,,not sure.

thanks,

Well, if you simply create a Unique Index on the SSN field and write an
Append query, the duplicates will be automatically rejected. However, you
need to know that under rare circumstances, legitmate duplicates of Social
Security numbers can exist.

I have an import button that imports data from excel to my access database
which is viewable in a form. My form is in datasheet view. There can be as

[quoted text clipped - 7 lines]

Thanks,
Randy

 

[Michael J. Strickland]

yes, but I'm importing new records into an exisitng table. How do I code
to
list the duplicates found before running the append query?

After making the SSN number field of your existing table a primary key (as
suggested by Roger), import the new records to a different (TEMP) table.

Then copy this table to the end of your table.

The non-duplicates will be copied and the duplicates will be placed in a
Paste Errors table.

I'm thinking I might need a popup form to list any duplicates found so
that
they can be deleted first and then run the append query,,,not sure.

thanks,

Well, if you simply create a Unique Index on the SSN field and write an
Append query, the duplicates will be automatically rejected. However, you
need to know that under rare circumstances, legitmate duplicates of Social
Security numbers can exist.

I have an import button that imports data from excel to my access
database
which is viewable in a form. My form is in datasheet view. There can
be as

[quoted text clipped - 7 lines]

Thanks,
Randy

--

 

[Roger Carlson]

It doesn't matter. You don't have to find the duplicates and delete them
before running the Append query. With a Unique Index on the SSN, a
duplicate value will be rejected regardless of how you try to add it.
Michael's method will work and has the added benefit of putting the errors
in the Paste Errors Table. But even if you are importing them directly to
the table, no records can be added that have a duplicate SSN as long as the
unique index is set.

BTW, while you CAN make the SSN the Primary Key, it doesn't have to be. All
that's necessary is that it has a unique index. This is done in the field
properties list under Index. Set the value to Yes (No Duplicates).

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

yes, but I'm importing new records into an exisitng table. How do I code to
list the duplicates found before running the append query?

I'm thinking I might need a popup form to list any duplicates found so that
they can be deleted first and then run the append query,,,not sure.

thanks,

Well, if you simply create a Unique Index on the SSN field and write an
Append query, the duplicates will be automatically rejected. However, you
need to know that under rare circumstances, legitmate duplicates of Social
Security numbers can exist.

I have an import button that imports data from excel to my access database
which is viewable in a form. My form is in datasheet view. There can

be as
[quoted text clipped - 7 lines]

Thanks,
Randy

 

[Marshall Barton]

I have an import button that imports data from excel to my access database
which is viewable in a form. My form is in datasheet view. There can be as
many as 900 records. Once the new data is in the form, I need to check the
SSN field against the exisitng table for duplicates and then list those
records that are duplicates. If no duplicates, then append those records to
the table.

Can you link to the Excel data or are you using a complex
code procedure to select the data from Excel?

If you can link to it, then it's a simple matter of doing an
unmatched query to select the Excel records to add to you
Access table. Then it's just a matter of reversing the
criteria in that query to create a duplicates query to tell
you which Excel data already exists in the Access table.