How to Delete Duplicates from a Table?? 2

[paramit81]

Hi All,
I have a table which has around 1500 records and I dont want to go through each record looking for duplicate values. But I am pretty sure, that there are some duplicate records. Is there anyway I can delete the duplicate records from the table, leaving just 1 unique record, with the help of a query? If so please enlighten me on the same.
thnx.

 

[PHV]

Create a copy of your table, say myCopy
Delete all the records in the original table:
DELETE FROM myTable;
In table design view create an index not allowing dups on the relevant fields
Paste-Append the content of myCopy to your table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Do a query like:

SELECT DISTINCT * FROM myTable;

Then change the query into a table.

SELECT qryWhatever.* INTO tblNew
FROM qryWhatever;

 

[paramit81]

Thanx PHV,
That helped. But I have another question. My table consists of First_Name and Last_Name fields, which should not have duplicates when considered as a combination, but can have duplicates when considered separate. For e.g.
First Name: David Last Name: Giles
First Name: Robert Last Name: Giles

I dont want 2 David Giles, but I definitely want David Giles and Robert Giles to be present in the database. When I performed the steps that u suggested, it deletes both David Giles and Robert Giles.
Any suggestions on that?
- Paramit81

 

[PHV]

create an index not allowing dups on the relevant field[highlight]s[/highlight]
The composite unique index must be created on First Name AND Last Name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Try what I did, it only deletes records that are exactly the same.

 

[sblanche]

This may be cheating a bit but it works for me. I just use the "Find Duplicates Query Wizzard" and it asks you what fields might be the same so you can select both first and last name.

SLB