Delete Dupicate records

[jnordhaus]

I have a set of data that has dupicate records in it and I want to leave
only one instance of the record and delete all the other records. When I use
the wizard in Access it pull all the records.
How do I write the code to delete the dupicates but leave the first instance
of the record?
Here is my code

SELECT DISTINCTROW First([LMU Data Table].[Job#]) AS [Job# Field],
Count([LMU Data Table].[Job#]) AS NumberOfDups
FROM [LMU Data Table]
GROUP BY [LMU Data Table].[Job#]
HAVING (((Count([LMU Data Table].[Job#]))>1));

 

[Tom Ellison]

Dear Nordhaus:

Before you can remove all the rows other than the "first instance" you
must define which one is the "first instance." This must be defined
using data in the columns of your table. I can see nothing about how
you might do this from your post here.

If you are expecting that the database somehow knows which record is
the "first instance" from each set of duplicates, you are sadly
mistaken.

Also, please consider all the columns other than Job#. All the
information in these other columns will be lost in the records you
delete. Is it safe to just discard all this information?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts