Deleting Duplicate Serial Numbers in Access Field (2003)

[N2Deep]

I have a table named SUPPORT DATA, I have a field named Serial Number. In the Serial Number field I have many duplicates, and I only want one of each.

Sample serials ABB045000MG, JBX05050016

Until now I have been running an update query which identifies the duplicate serial numbers in the SUPPORT DATA table by:

In (SELECT [Serial number] FROM [SUPPORT DATA] As Tmp GROUP BY [serial number] HAVING Count(*)>1 ) And Is Not Null

When it finds duplicates it updates a field called Duplicate with "TRUE", in the SUPPORT DATA table, I then query the SUPPORT DATA for "TRUE", cut and paste those records into an Excel workbook, run a macro which elimates the duplicates, then paste them into an append query which deletes the duplicate serials from the SUPPORT DATA table.

I know there is a better way then having to go outside Access to do this.

Thanks!

[RuralGuy]

Just a thought but if you create a table that duplicates your existing table but also has a "no duplicates index" on the serial number field, you can append your table to this table and only the first serial number will be allowed.

[RuralGuy]

I should also ask how the duplicates are getting in the table in the first place.

[N2Deep]

Thanks for your response.

I'm importing .txt files into Access, and that is where the duplicates are coming in from.

[NeoPa]

You don't say how you determine which of the multiple lines (duplicates) should be kept as the correct item.

Otherwise, it's perfectly possible to do what you need in Access. We just need a more precisely defined explanation of what you need.