Remove Duplicate Records

[Ganley]

Can anyone suggest a way to remove duplicate records.

I've come up with this:
SELECT *, count(*) cnt FROM tableName GROUP BY fieldname1, fieldname2, .... HAVING cnt > 1

But this does not fully resolve my problem.


Thanks.

 

[gemma-the-husky]

try creating an new table with a key that will exclude your duplicates, then just copy and paste all the records - the duplicates will fail

then rename the new table to the old table name.

 

[Ganley]

Thanks Gemma.

You put me on the right track.

This is what i ended up doing.

Create a new query and bring in the table in question. Drag in both the field or fields that contain the duplicate data and the unique key for that table. Set on the totals for the query by clicking the summation symbol on the toolbar or by choosing view¦totals from the menus. In the group by row for the unique key choose first. This will return the first record for each group of records. Save this query as qry_List_Of_Firsts

Create a second query. Bring in the qry_List_Of_Firsts and the original table. Then link them by unique key from the unique table to the qry_List_Of_First. Double click the join and choose the second option which shows all records from original table and only those records from qry_List_Of_Firsts that match. Drag in the unique keys from both tables. Click off the show button for the qry_List_Of_Firsts and set it's criteria to IS NULL. This will list all the records in the original table that are not in the qry_List_Of_Firsts. Save this new query as qry_List_To_Delete. This new query now contains every record that is a duplicate that is not the first record for that set of duplicates.

Create a third query and bring in just the Original table. Set the query type to DELETE, and drag in the star field * (all fields) and the unique id. Click off the show box for the unique id and for it's criteria use DLookUp("UniqueFieldName","qry_List_To_Delete","UniqueFieldName =" & [UniqueFieldName])