Delete query

[Domac]

Hi,


I need to delete duplicates from table, i have located records that are
duplicated using count >1.
But it returns about 200 records , so it would be gruelling to delete it by
hand.


So i need a query that goes about like this:


DELETE First(*) where Exist (MyDuplicateRecordListQuery)

Can I use First in this context??

Please help.

 

[Domac]

Solved it !!!


Thanks!

 

[Arvin Meyer [MVP]]

I would first identify the records you want to delete by the duplicate
value(s) and the First of their Primary Key value:

SELECT DISTINCTROW LastName, First(StudentID) AS FirstID
FROM tblStudents
GROUP BY tblStudents.LastName
HAVING (((LastName) In (SELECT [LastName] FROM [tblStudents] As Tmp GROUP BY
[LastName])));

Now, you have the ID values, which I'd write to a new temporary table. Link
the temporary table back to the original table on the Primary Key, and run
the query. Delete anything that you find, and compact the database.