How to delete "non-identical" duplicate records in an Access table

[Ernest Monterrosa]

How to delete "non-identical" duplicate records in an Access Table? Where
"non-identical" duplicate record means a record in the table that has
slightly different datum in one of the fields, but an identical duplicate
datum in the field that I am concerned with. For example:
SSN MRN CLIENT NAME
001-00-2222 11170419 Smith, Jane
001-00-2222 11170419 Smith, Jane T
001-00-2222 11170419 Smith, Jane Thompson

The data of these two records in the fields SSN and MRN are identical; but
"non-identical" in the CLIENT NAME field (notice the “T†and “Thompson†in
the second and third row)
I am concerned with MRN field, and I consider these two records a duplicate
because the MRN data is identical.

I need to know a way in Access to delete all “non-identical†records EXCEPT
ONE.

 

[mscertified]

Since they are non-identical, how to decide which to delete???

-Dorian

 

[Jerry Whittle]

Is there a primary key field for that table? That could help a lot. There is
a brut force method:

First make a complete backup of the database and put it away for safe
keeping in case something goes wrong.

Next create a Totals query that eliminates the dupes by picking just one of
the CLIENT NAMEs. I used the Min function, but the Max, First, or Last may
also do the job. Then convert this query to a Make Table that pours the
resulting records into it. Something like below:

SELECT Ernest.SSN,
Ernest.MRN,
Min(Ernest.[CLIENT NAME]) AS [CLIENT NAME]
INTO Ernest2
FROM Ernest
GROUP BY Ernest.SSN, Ernest.MRN;

Note: Change "Ernest" to the actual table name.

After running the make table query, open the new table and closely inspect
the results. If they are to your satisfaction, delete the original "Ernest"
table and rename the new table to "Ernest".

The above procedure will work if there are only the three fields that you
mentioned. You'll have to make some decisions if there are more fields. Also
it doesn't take into account things like relationships and referiential
integrity which may prevent you from deleting the original "Ernest" table or
make a mess of your queries. That's why I highly recommend making a backup.

 

[Michael Gramelspacher]

On Wed, 12 Dec 2007 06:40:01 -0800, Ernest Monterrosa <Ernest

How to delete "non-identical" duplicate records in an Access Table? Where
"non-identical" duplicate record means a record in the table that has
slightly different datum in one of the fields, but an identical duplicate
datum in the field that I am concerned with. For example:
SSN MRN CLIENT NAME
001-00-2222 11170419 Smith, Jane
001-00-2222 11170419 Smith, Jane T
001-00-2222 11170419 Smith, Jane Thompson

The data of these two records in the fields SSN and MRN are identical; but
"non-identical" in the CLIENT NAME field (notice the “T” and “Thompson” in
the second and third row)
I am concerned with MRN field, and I consider these two records a duplicate
because the MRN data is identical.

I need to know a way in Access to delete all “non-identical” records EXCEPT
ONE.

possibly:

SSN MRN Client Name
001-00-2222 11170419 Smith, Jane
001-00-2222 11170419 Smith, Jane T.
001-00-2222 11170419 Smith, Jane Thompson
002-01-3333 22220519 Mouse, Mickey
002-01-3333 22220519 Mouse, Micky
002-01-3333 22220519 Mouse, Michael
003-03-1234 33311999 Thomas, Bart

DELETE *
FROM Table1
WHERE [Client Name] In (
SELECT b.[Client Name] FROM Table1 AS a
INNER JOIN Table1 AS b
ON len(a.[Client Name]) > len(b.[Client Name])
AND a.SSN = b.SSN
AND a.MRN = b.MRN);

SSN MRN Client Name
001-00-2222 11170419 Smith, Jane Thompson
002-01-3333 22220519 Mouse, Michael
003-03-1234 33311999 Thomas, Bart