[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Removing duplicate rows from Access 2003 Table

Posted on 2007-11-27
6
Medium Priority
?
4,618 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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 using thread_statistics can cause high memory usage.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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