• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Finding Duplicate Rows

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?
0
k209310
Asked:
k209310
1 Solution
 
Arthur_WoodCommented:
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.

AW
0
 
k209310Author Commented:
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?
0
 
VolibrawlCommented:
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.
 
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
VolibrawlCommented:
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).
 
0
 
Arthur_WoodCommented:
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.

AW
0
 
1WilliamCommented:
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.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer
0
 
Arthur_WoodCommented:
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.

AW
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now