Solved

remove all but ONE duplicate entry

Posted on 2003-11-07
4
567 Views
Last Modified: 2012-05-04
by default if you run an unmatched query eg " table" against "find duplicates for table" it removes all the duplicate entries, effectively removing some rows with data that i want.  I would like to be able to keep only one of the duplicate entries in the finished list.

from this:

name1 phone1
name2 phone2
name2 phone2
name3 phone3

to this:

name1 phone1
name2 phone2
name3 phone3

NOT this:(which the unmatched query i spoke of results in)

name1 phone1
name3 phone3

any ideas?
0
Comment
Question by:NDSgroup
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9700426
To do this you employ a trick.

Create a copy of the table structure (not the data); make sure that the fields that identify duplicates are all part of a unique index.
Run an append query which appends all records from your old table to your new table.
Delete your old table and rename the new one.

(To copy a table structure, right-click the table name in the databse window, and copy and paste to a new named table)

Pete
0
 

Expert Comment

by:senan072100
ID: 9701480
if I understand you well, what about adding select distinct to the first query, by thoing this u will get ride of douplicated records, then run the output against the unmatched query, just an idea ???!!!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9701744
If your problem is that you have records which are complete duplicates (every field is the same) then you can use select distnct * to select records  Distinct looks at every selected field in your select statement.  You could use this as the basis of a make table or append query.

I did not anticipate this being the problem.  Most issues of this type are 'duplicate key' problems.

But if you have complete duplicates then there can't be a field to match on with your original table, since by definition this would mean that the records were not duplicates.  

Pete



0
 
LVL 1

Accepted Solution

by:
paul_st earned 250 total points
ID: 9705727
In order to do what your asking, your table will need to have a primary key, for example in your table you would have three fields (ID, Name, Phone) where ID is the primary key, ID could be an autonumber field. You could then execute the following SQL statement to remove the duplicate (name, phone) tuples:

DELETE * FROM Table1
WHERE ID IN
(SELECT DISTINCT A.ID FROM Table1 AS A, Table1 AS B
WHERE A.Name=B.Name AND A.Phone=B.Phone AND A.ID<B.ID)

Executing the query will keep the record with the greatest ID and delete all the others. For example if you had
1 Name1 Phone1
2 Name1 Phone1
3 Name1 Phone1

The following records would be deleted:
1 Name1 Phone1
2 Name1 Phone1

Leaving you with:
3 Name1 Phone1
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

930 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

15 Experts available now in Live!

Get 1:1 Help Now