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

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.

0
Utf
12/12/2007 2:40:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1486 Views

Similar Articles

[PageSpeed] 33

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

-Dorian

"Ernest Monterrosa" wrote:

> 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.
> 
0
Utf
12/12/2007 3:31:03 PM
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.

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Ernest Monterrosa" wrote:

> 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.
> 
0
Utf
12/12/2007 5:27:03 PM
On Wed, 12 Dec 2007 06:40:01 -0800, Ernest Monterrosa <Ernest
Monterrosa@discussions.microsoft.com> wrote:

>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
0
Michael
12/12/2007 7:44:56 PM
Reply:

Similar Artilces:

How to delete "non-identical" duplicate records in an Access table
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...