Using Social Security Numbers, but how do I get rid of duplicates.

[Donna]

I loaded a file with participant ssn numbers, but there are duplicates. How
do I get rid of the duplicates to get a record count of pariticipants? I can
do basic queries in Access and need help.

 

[Roger Carlson]

Do you acutally want to get rid of the duplicates? Or just count unique
SSNs?

If the second option, you can write a query in the SQL view something like
this:

SELECT Count(SSN) AS CountOfSSN
FROM [SELECT SSN
FROM MyTable
GROUP BY SSN]. AS UniqueSSN;

Replace "MyTable" with your actual table name.

If you actually want to remove records with duplicate SSN, on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"RemoveDuplicates.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
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

 

[Donna]

I'm not familiar with SQL View, I'm realatively a novice with Access, simplet
queries, but I can try.

 

[John Spencer]

To do this in the grid takes two queries

First query gets a unique set of theSSN number
-- Build a query to return the SSN
-- In the query properties, set Unique Values to Yes
-- Save the query

Open a new query
-- use the saved query as the source
-- Add SSN to the field list
-- Select View: Totals from the menu
-- Change Group By to Count.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chefjay22]

Donna,

If you are not too technical I found the following method to be very
effective to get rid of dups.

I firt would copy the main table... then past it... structure only... no
data. Then I would go into design mode on the new table and set the SSN
field as the PK. Then you simply create an append query with the source
being the original table and the detination being the new table... when the
query runs it will append all records to the new table... but since the SSN
is the PK it will not allow for dups to enter. This is the non-technical way
to do this I have done this for years when I need something quick and
easy Hope this helps!

Jason