Finding Duplicate Rows

Posted on 2003-02-26
Medium Priority
Last Modified: 2012-05-04
I am looping through a database to find duplicates. I need to find duplicte rows. I can find duplicate cells with out too much problem. Is there any way to find duplicate rows of data?
Question by:k209310
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
LVL 44

Expert Comment

ID: 8025556
do you mean, by "duplicate row", that EVERY field has the same value - if that is YES, then this suggests that you do NOT have a PRIMARY KEY defined on this table, and that is BEGGING for trouble.

In Access, if you create a NEW query, and use the "Find Duplicatges Query Wizard" to do what you are asking.


Author Comment

ID: 8025626
Dont talk to me about the primary key. Its an old databse with thousands of rows of data and who knows why - but there is no primary key. I need to delete entire rows if they are duplicates.

I am using vb to loop through two tables to compare recordsets if a duplicate then nothing happens else an append query takes place.

I know you can use
if recordset!Field1 = recordset!field2 then...

Would i have to do this for every field in the recordset or would is there a way (in VB) to find entire rows of duplicate information?

Expert Comment

ID: 8026099
You would have to do it for every field.

There is no reason why you can't introduce a primary key for your newly adjusted table.  Since there is none at present, you can just go into table design and add an ID (likely autonumber would be best) and designate it as the primary key.  The additional field should not affect anything you already have and it will automatically take care of itself as far as existing or new records.  It will be there when you decide to use it.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Expert Comment

ID: 8026200
btw, the time you decide to use it could well be right now.  If you did a "series" of duplicate queries, you could filter down to TRUE duplicates, but at that point, you would have no way of identifying which records you were looking at (unless you had a primary key).
LVL 44

Accepted Solution

Arthur_Wood earned 200 total points
ID: 8026288
as I said, why not simply open the MDB, create a New "Find Dupicates Query" (suing the wizard, and then you can delete all but one of the duplicatge records that result.  Other than taht, you will need to test INDIVIDAULLY, each and Every field of one recordset against the corresponding field of a second recordset, to determine true 'duplicates", as there is no other way to compare recordsets as a whole.

A recordset is really composed of a collection of field 'objects' and what, in you case, makes the two recordset identical is that the contents of each of the field objects in one collection is exactly the same as the contents of the corresponding field object in the other recordset's fields collection, and that con only be test one field at a time.

LVL 18

Expert Comment

ID: 8743208
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accpet questionm, split points to Arthur_Wood@devx and Volibrawl
Please leave any comments here within the next seven days.
EE Cleanup Volunteer
LVL 44

Expert Comment

ID: 8754456
after my answer (which you have now 'accepted' with a grade of C) you NEVER EVER asked for any additional assistance. If the answer was not sufficient, then you should have asked for additional assistance.

 If you are going to behave that way, then it is VERY unlikely that anyone here will ever offer you assistance in the future.  A C grade is considered a SLAP-IN-THE-FACE, and I, for one, will avoid offering you help in the future.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

741 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