472,356 Members | 708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,356 software developers and data experts.

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.

Aug 2 '08 #1
4 2063
375 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
375 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
32,511 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

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

Similar topics

by: xool | last post by:
Hi all, can anyone tell me how to delete just the current record if tempdate >= todaydate then Set newMail=Server.CreateObject("CDONTS.newMail") newMail.to = rs.fields("Email") newMail.From =...
by: Stewart Allen | last post by:
Hi there I'm trying to find part serial numbers between 2 numbers. The user selects a part number from a combo box and then enters a range of serial numbers into 2 text boxes and the resulting...
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
by: Iona | last post by:
Hi Allan, I'm using a nifty piece of code you put on here some time back to do a duplicate entry check as below. I'm using to check for duplicate names. However I am getting an error message on...
by: mivey4 | last post by:
Okay I have 2 tables: Table A - holds a list of new hardware serial numbers and their corresponding model (no constraints or indexes) Table B - holds a distinct list of current serial numbers...
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
by: KPoe | last post by:
Hi, I have a simple Access Table (LstFax) with "REF" (Client NAme) and "ADDR" (Fax numbers) fields that has been generated by SQL from a custom query form. There are duplicate fax numbers for...
by: HSXWillH | last post by:
This is kind of an odd question, I'm hoping the answer is easy for Access 2003. I do design for horse racing data dumps and in trying to group certain types of race information, I find many minute...
by: AXESMI59 | last post by:
have a project in which I am entering Serial Numbers and Date codes into a Combo box. Serial numbers are all different. However, they could each have the same Date Code. Each Serial Number has a...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.