Delete duplicates in access

[prinand]

I have a SQL query in Access which selects the duplicate entries.

how should the proper format be to delete the duplicates, and keep only one entry. I would like to have it as a queury which I can run every now and then...

the query to select the duplicate records is :

SELECT tArcservelog.id
FROM tArcservelog
WHERE (((tArcservelog.Date_time) In (SELECT [Date_time] FROM [tArcservelog] As Tmp GROUP BY [Date_time],[Jobno],[HistoryName],[Alertno] HAVING Count(*)>1 And [Jobno] = [tArcservelog].[Jobno] And [HistoryName] = [tArcservelog].[HistoryName] And [Alertno] = [tArcservelog].[Alertno])))
ORDER BY tArcservelog.Date_time, tArcservelog.Jobno, tArcservelog.HistoryName, tArcservelog.Alertno;


so if the above is called "duplicates" I assume it would be something like

Delete * from tArcservelog where id in ("duplicates")

(id is an autonumber field so that is always unique and the perfect method to select one record)

I tried something like above and get a warning that I have to use EXISTS somehere in the statement ???

And one more thing which worries me : I need to leave 1 entry... and I am a bit scared that what I suggest above in the current format, will delete all the duplicate records, leaving no entry of the records at all...

so if someone could provide the proper SQL format, I would be very happy...

 

[BobStubbs]

A good way to generate SQL for this, is to run the Find Duplicates Query wizard. Let the Wizard create a query to list duplicate records in your table, then switch to SQL View to see the underlying SQL code.

If you add a Yes/No field to your table, you can then amend the query to set this field to 'yes' on the duplicate records. If you then view all the records in the table, you can reassure yourself that the code is just marking duplicates, and not every record.

Finally, when you are satisfied that the SQL code is doing exactly what you require, you can copy the SQL code elsewhere, as required.

I hope that this helps.


Bob Stubbs