467,076 Members | 1,027 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,076 developers. It's quick & easy.

Deleting Duplicate Serial Numbers in Access Field (2003)

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!
Aug 2 '08 #1
  • viewed: 1777
Share:
4 Replies
RuralGuy
Expert 256MB
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.
Aug 2 '08 #2
RuralGuy
Expert 256MB
I should also ask how the duplicates are getting in the table in the first place.
Aug 2 '08 #3
Thanks for your response.

I'm importing .txt files into Access, and that is where the duplicates are coming in from.
Aug 2 '08 #4
NeoPa
Expert Mod 16PB
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.
Aug 4 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by xool | last post: by
3 posts views Thread by Stewart Allen | last post: by
3 posts views Thread by Nathan Bloom | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.