Delete Dups using an SQL Query

[Guest_imported]

Can someone tell me how I would write a delete duplicate
SQL query within Access97

Here is a "find duplicate query" EX:
SELECT DISTINCTROW First(MAIN.parcel_no) AS [parcel_no Field], Count(MAIN.parcel_no) AS DUPLICATE
FROM MAIN
GROUP BY MAIN.parcel_no
HAVING (((Count(MAIN.parcel_no))>1));

I tried modifying this to delete w/ no luck
any help would be greatly appeciated
thanks,
scotta

 

[MichaelRed]

The problem with using a find duplicates query to DO the deletion is that - by deffinition - you find ALL of the duplicate instances. In hte general case, you DO NOT want to delete all of hte records, - only the "Extra" ones. I generally use the 'find dups' query to identify the duplicates - but a procedure to do the deletion.

With the 'find dups' query as a record source:
[tab]loop through the list.
[tab]Find the first record matching the query record.
[tab]find the next record matching the query record and delete it
[tab]repeat untill there are no more matches
[tab]get the next query record
[tab]loop
End With

Do this on a test table untill you are VERY COMFORTABLE with the results. Ms. Access does not support 'undo' in this context.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Here is a different approach.[ol][li]Create a table that matches the structure of the table with duplicates.
[li]Create a primary key on the new table with the columns you used to identify duplicates.
[li]Insert (or append) all rows from the old table into the new table. Some will not insert because they are duplicates.
[li]Delete or rename the old table.
[li]Rename the new table to the old table name.[/ol]You should have no duplicates in the table. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin