Duplicate Records

[monbri]

This might be an odd problem. I have a table with 19049 records. 8904 are duplicates. I want to delete the duplicates and the origianals and just leave the records that are not duplicates leaving me with 1241 records.

Any ideas?
Thanks in advance.

Brian
Fort Lauderdale, FL

 

[Joe4]

In a query, if you group the records based on all fields, it will group all like records, thus eliminating the duplicates (from the query only).

You can then use this query as your data source, or create a Make Table Query to write these records back to a new table, if needed.

 

[monbri]

Jmiskey,

I am not exactly sure what you mean. Anyway you could dummy this up for me?

 

[Joe4]

Create a new query. Select the table with the duplicates. Then add every field in your table to the query separately (do not use the asterisk option). Then click on the "Totals" icon button (it looks like a Summation sign, or fancy looking "E"). Then view your data. This will group all duplicate records into one record.

 

[bat17]

As Monbri wants to exclude the original as well as the duplicate he will need to set some sort of count field as well to use as a filter

Peter

 

[Abulafia]

All you have to do is use this SQL but changing the MyTable and MyField variables to match your table/field.

SELECT First(MyTable.MyField) AS [Field], Count(MyTable.MyField) AS NumberOfDups
FROM MyTable
GROUP BY MyTable.MyField
HAVING (((Count(MyTable.MyField.))>=1));

to get all the records broken into groups.

Change the query to a MakeTable Query and you'd done it.