?
Solved

Removing duplicate rows from Access 2003 Table

Posted on 2007-11-27
6
Medium Priority
?
4,612 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 2000 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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