By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Deleting Duplicate Serial Numbers in Access Field (2003)

P: 10
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
Share this Question
Share on Google+
4 Replies


RuralGuy
Expert 100+
P: 375
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 100+
P: 375
I should also ask how the duplicates are getting in the table in the first place.
Aug 2 '08 #3

P: 10
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 15k+
P: 31,494
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.