Solved

Removing duplicate rows from Access 2003 Table

Posted on 2007-11-27
6
4,557 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

17 Experts available now in Live!

Get 1:1 Help Now