Locating duplicate entries in the same table (1 Viewer)

triplell89 · Jul 10, 2009

Hi,

I'm trying to create a query that will search through my table and return any rows that have matching fields in a different row of that same table. (or partial matching fields is my final goal, however obtaining the query is my first objective)

Here is an example Table

John Smith 842
Jan Smith 333
Joe Smith 434
June Smith 842
Jack Smith 333

Here is what I want as the result:

John Smith 842
June Smith 842
Jan Smith 333
Jack Smith 333

I know I can create some VBA for excel that could do this, but I would like to learn how to do this purely in access.

Thanks

jzwp22 · Jul 10, 2009

You will want to use the Find Duplicates Query Wizard. The query will look something like this (SQL view) as an example.

Code:

SELECT Table1.mynumber, Table1.ID, Table1.empname
FROM Table1
WHERE (((Table1.mynumber) In (SELECT [mynumber] FROM [Table1] As Tmp GROUP BY [mynumber] HAVING Count(*)>1 )))
ORDER BY Table1.mynumber;

triplell89 · Jul 10, 2009

Ok, now I want it to only display 1 instance of the duplicates, and also be able to update the customer id's to a new table.

I have a table that has all the customers and all their id's, which is used as a reference table. There have been about 900 new customer id's added this, and adding them by hand isn't much of an option.

jzwp22 · Jul 10, 2009

Which duplicate instance do you want to display? How would you determine which is the correct one to display? Are there related records in other tables that correspond?

You could take the results from the Find Duplicates Query Wizard and edit one of each set of duplicates to a new number manually (assuming that the new numbers will not duplicate any existing records).

I'm not sure what you mean by this:

update the customer id's to a new table.

Do you mean once you have the data cleaned up you want to append the records from the cleanup table to another table?

triplell89 · Jul 10, 2009

jzwp22 said:
Which duplicate instance do you want to display? How would you determine which is the correct one to display? Are there related records in other tables that correspond?

You could take the results from the Find Duplicates Query Wizard and edit one of each set of duplicates to a new number manually (assuming that the new numbers will not duplicate any existing records).

I'm not sure what you mean by this:

Do you mean once you have the data cleaned up you want to append the records from the cleanup table to another table?

This is what I have: Customer Name, Customer ID. The problem is I generated this list from active customer transactions, which use different systems where the customer name can be slightly different. Because of this, I have instances like this:

Triplell89 xx4sx43
Tripplell89 xx4sx43

I want add to the new table all the new customers, however there are 900 of them as of now(with some duplicates). How do I get it so it will assign the first instance of a duplicate name to the ID, and neglect any other instances of that ID after that?

jzwp22 · Jul 10, 2009

How many of the 900 are duplicated? I would think that you should evaluate each set of duplicates and delete the ones that have errors (spelling or otherwise) rather than just taking the first instance. Otherwise, you will be transerring bad data into you new table. Once you have it cleaned up, appending the data to a new table can be handled with 1 append query.

I am drawing a blank on how to do this with queries (nested queries would be needed). This can be done with some fairly simple code, but I would need the actual table and field names of your original table and the new table. Can you post your database? If you want, you can create a copy of the database, delete all of the data & post that. I will try to work on it over the weekend.

triplell89 · Jul 10, 2009

It's nothing I want you to lose sleep over. I just assumed there was a quick way to doing this, and as of now I have to do it on a monthly basis.

Our database is full of bad data, and it really is a shame, but it's not a top priority for us right now. We're trying to set up conventional naming standards, however going through and changing the 200k names in our system is apparently not feasable. It does drive me nuts, but I'm just a peon and there is really nothing I can do to change it. I have a feeling that one day it's going to kick us in the ass.

Thanks for your help, but I'll continue to use excel to manipulate my databases until I figure out how to do it in access(I'm still learning)

jzwp22 · Jul 10, 2009

I have a feeling that one day it's going to kick us in the ass.

A similar situation just happened today where I work.

If you are going to do this on a regular basis, you also want to make sure that when you transfer the next month's data to your main table you don't end up creating duplicates in your main table. Just a gut feeling, but I think doing both operations (finding duplicates in the imported data and preventing new duplicates in the main table) will best be handled by code.

Locating duplicate entries in the same table (1 Viewer)

triplell89

Registered User.

jzwp22

Access Hobbyist

triplell89

Registered User.

jzwp22

Access Hobbyist

triplell89

Registered User.

jzwp22

Access Hobbyist

triplell89

Registered User.

jzwp22

Access Hobbyist

Similar threads

Users who are viewing this thread