Find Duplicate Records

[Angelique]

Does anyone have a query and/or sql that finds duplicate records? I have a table that has 4 key fields to create a unique key. One of the fields can become a duplicate if imported from a standalone when one of the fields isn't exactly the same (therefore not a key violation).


Angelique

 

[JoeNg]

Assume you have a table like this:

Fld1 Fld2 Fld3 Fld4
1 1 1 1
1 1 1 2
1 2 1 3
1 1 1 4
1 1 1 1

Fld1, Fld2, Fld4, Fld4 are your composite key fields. That is, the fields for your key.

Note Record 1 and record 5 are the same.

The query to find duplicated values in, let say, Fld4 is:


T1.DB | Fld1 | Fld2 | Fld3 | Fld4 |
| Check | Check | Check | Check calc count all |


Your results:
Fld1 Fld2 Fld3 Fld4 Count of Fld4
1 1 1 1 2.00
1 1 1 2 1.00
1 1 1 4 1.00
1 2 1 3 1.00


In Count of Fld4 column, value > 1 are records with duplicated values in Fld4.


Now, if you are running into key violation problem. I believe you have a design problem and should consider using different fields for your key.

 

[Angelique]

Hi Joe

I seem to inherit badly designed databases these days! What I am looking for is a duplication of Inc No with a different Date:

Stn No Inc No Fld3 Date
1 1 1 1
1 2 1 2
1 3 1 3
1 4 1 4
1 1 1 5

In my example you will not that Inc No 1 is duplicated but because Date is different, the database considers it to be unique. But the user needs to be able to see that there are two (2) Inc No#1.

I will try your query.


Angelique

 

[ros]

>The query to find duplicated values in, let say, Fld4 is:
>
>
> T1.DB | Fld1 | Fld2 | Fld3 | Fld4 |
> | Check | Check | Check | Check calc count all |

Don't check the field you wish to count

should be:
T1.DB | Fld1 | Fld2 | Fld3 | Fld4 |
| Check | Check | Check | calc count all |

Richie

 

[JoeNg]

ros's query is correct if to find dups on "Inc No" field.

 

[Angelique]

Richie,

I tried the query you suggested with the knowledge that I had a duplicated incident no with success.

Angelique

 

[zeebow]

Angelique,

I guess you can try using 'SELECT DISTINCT' in sql, since all fields are part of the primary key.

 

[vtj]

The easiest way to find a duplicate record is to run a query and select the key value(s) only.

Rename the answer table and then restructure the new table and select all the fields as the key. When you save the new table Paradox will generate a Keyviol.db which should have the extra values. VTJ