Solved

Removing duplicate rows from Access 2003 Table

Posted on 2007-11-27
6
4,572 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now