Solved

Removing duplicate rows from Access 2003 Table

Posted on 2007-11-27
6
4,606 Views
Last Modified: 2008-02-01
Hi,
I have a query table in Access 2003 (Table1). The first column has duplicate data. I want to remove the duplicate rows from the table (and keep one row only for the duplicate data). What sql code do I need for a query to do this?
Thanks
Sam
0
Comment
Question by:samirst
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20356897
There is no simple code to do this.
A common approach is to copy the table structure and in the new table  add a unique index to the field that identifies the duplicate. Then use an append query to add all current records to the new table.
Duplicates will be rejected and you will get an error message telling you this.

You can then either rename the tables or delete all records  from the original table, set up the index, and use another append query to send the valid records back.

Make sure you have a backup first.
0
 
LVL 3

Expert Comment

by:JeffvClayton
ID: 20356962
Heres another way

SELECT First(Table1.ID) AS FirstOfID INTO Table2 FROM Table1 GROUP BY Table1.Test;

Table 1 has 2 columns, a primary key ID column  and a  column with some rows that are duplicates. The SQL extracts the first unique occurence of each row and puts the  results in a new table - i.e. a table that has only 1 occurence of each item

You then join the resulting table with the original and delete everything where the ID columns dont have a match OR just use the new table and delete the original
0
 
LVL 3

Expert Comment

by:JeffvClayton
ID: 20356995
I should have mentioned my first primary key ID column is a unique Autonumber field which is always useful to have as the first field of a table
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:samirst
ID: 20357077
Thanks for the comments, Peter57r - your solution works fine, but the data is already in Access, I want to avoid importing and exporting the data bacause I need to query the data in access first. I can export data to excel, and import it back into an access table using the column with duplicates as a primary key, this will not import the duplicates. But I wanted a sql solution.
JeffvClayton - your solution partly works as the new table is created, but itis giving an incorrect number of non duplicate records. The non duplicate records I should have (tested and checked in excel) is 1613, your solution provides 1222 with the difference missing. Any ideas?
Thanks
Sam
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 20357116
My solution does not involve exporting or importing.

JeffvClayton's solution requires you to have a unique (non-duplicate!!) record ID field - you need to add this to your table as an autonumber field if you haven't already got one.
0
 

Author Comment

by:samirst
ID: 20357138
Ahh.... yes Peter, after reading your solution again, it does seem  easier as all it needs is an append query into a new table with a unique primary key .... tried it and works well gtiving the correct number of records. Thanks
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question