Help dealing with duplicate records

[jswenson3]

Okay experts, I need some help... here's my problem:



My team currently manages a couple hundred files as Excel files, standardizing data and removing duplicates as needed. We take these files, combine them all into one larger file and then remove duplicate records for our processing. Our processing adds a status field to the record when complete to id what happened to that record and we then return that status to the source files.


How can I remove the duplicate records that we normally remove before processing, and apply the record status field to these.

Keep in mind the duplicates are not exact matches, I consider loose matches on name, address, phone, etc as duplicates.

Any ideas??

[bigroo]

jswenson3,

Okay experts, I need some help... here's my problem:

My team currently manages a couple hundred files as Excel files, standardizing data and removing duplicates as needed. We take these files, combine them all into one larger file and then remove duplicate records for our processing. Our processing adds a status field to the record when complete to id what happened to that record and we then return that status to the source files.


How can I remove the duplicate records that we normally remove before processing, and apply the record status field to these.

Keep in mind the duplicates are not exact matches, I consider loose matches on name, address, phone, etc as duplicates.

Any ideas??

I take it you import all the files into Access for processing?
You make no mention of a database in your note, just asking, or are you handling this in Excel?

Alos, what do you conside "Loose Matches"?

Once we have the answer to the above we can reply better with a possible solution!

Thanks

[jswenson3]

jswenson3,



I take it you import all the files into Access for processing?
You make no mention of a database in your note, just asking, or are you handling this in Excel?

Alos, what do you conside "Loose Matches"?

Once we have the answer to the above we can reply better with a possible solution!

Thanks

We currently manage everything in Excel. Ugh! I'd like to use Access to remove some of the manual processing we have to do to look for these duplicate records, removing them, and putting them back in at the end of the process.

We consider tight matches to be nearly exact matches on company name, address, city, st, zip, and phone. Loose matches would be considered similar records, i.e. 123 main street - 123 main st; or abc plumbing inc - abc plumbing. LLC, Inc., Corp. any of these missing or abbreviated among other things like that.

hope this is clear enough.

[bigroo]

jswenson3,

I think for me, I would use Access, link the excel to Accees via Linked tables, this will get the information into Access and from there I would create another exactky as the linked table is, structure only and setup some unique keys on the fields you want no duplicates within.

before you get to that point, I would run the data through some queries to cleanup the data and add the status field as you want.

Once done, append data to the new table, all duplicates can be removed.

I hope this helps!!

[jswenson3]

unfortunately i can't clean up the records at all. They need to remain as is (right or wrong as it may be) in order to return when all processing is complete. long story short, i need to take file 1, move any duplicate records to file 2, run file 1 through processing to append pass/fail status to original records, and then add pass/fail status to the duplicate records.

Major headache here!!

[bigroo]

Hi jswenson3,

Well, if you are not going to update any data in file1, use the append query to add the unique rows to the new table.

Then create an unmatch query comparing what you have in the new table to the linked table, what is left in this query will be the data for your file 2.

Change to update query and update the status field as you need.

If anyone has another approach??

[jswenson3]

found this SQL statement in an MS KB page, but something is wrong. anything stick out?



SELECT source.[Company Name], source.[Address], source.[Suite], source.[City], source.[ST], source.[ZIP], source.[Phone #], source.[Uniquicity]
FROM source
WHERE (((source.[Company Name]) In (SELECT [Company Name] FROM [source] As Tmp GROUP BY [Company Name],Left([Address],7),[Suite],[City],[ST],[ZIP],[Phone #] HAVING Count(*)>1 And Left([Address],7) = Left([source].[Address],7) And [Suite] = [source].[Suite] And [City] = [source].[City] And [ST] = [source].[ST] And [ZIP] = [source].[ZIP] And [Phone #] = [source].[Phone #])))
ORDER BY source.[Company Name], source.[Address], source.[Suite], source.[City], source.[ST], source.[ZIP], source.[Phone #];

It seems to be missing several dupes within the source file.

[bigroo]

Hi jswenson3,

This SQL statement is for finding duplicate records in Source table.

Why do you say

found this SQL statement in an MS KB page, but something is wrong. anything stick out?

Just curious?

[jswenson3]

Okay, keep in mind that I'm a noob here, but it seems to be missing several duplicate records on my source file. I need to remove duplicate company name matches up to 5 characters as well but i don't know where to specify that in this SQL statement.

[bigroo]

Hi jswenson3,

Sorry, I did not mean any dis-respect toward you!!

Try this,

SELECT source.[Company Name], source.[Address], source.[Suite], source.[City], source.[ST], source.[ZIP], source.[Phone #], source.[Uniquicity]
FROM source
WHERE (((source.[Company Name]) In (SELECT [Company Name] FROM [source] As Tmp GROUP BY [Company Name],Left([Address],7),[Suite],[City],[ST],[ZIP],[Phone #] HAVING Count(*)>1 And Left([Address],7) = Left([source].[Address],7) And [Suite] = [source].[Suite] And [City] = [source].[City] And [ST] = [source].[ST] And [ZIP] = [source].[ZIP] And [Phone #] = [source].[Phone #])))
WHERE (((Len([source].[Company Name]))<6))
ORDER BY source.[Company Name], source.[Address], source.[Suite], source.[City], source.[ST], source.[ZIP], source.[Phone #];

I am still learning SQL here as well.
I guess I find it easier to use the QBE, then look at the SQL!!

[jswenson3]

No worries bigroo, just wanted to make sure you knew how little i know about sql...

I'm looking through your SQL statement and it looks like it's looking for only company names that are <6 characters. SORRY! I'm actually needing matches to the first 5 characters of the company name as well as the first 7 of the address.

I think it's a small change, i'm just not sure where to place it.

THANKS!

[jswenson3]

I think I got it. Here's the statement i used.


SELECT source.* INTO [dupes after query]
FROM source
WHERE (((Left([source].[Company Name],5)) In (SELECT Left([Company Name],5) FROM [source] As Tmp GROUP BY Left([Company Name],5),Left([Address],7),[City],[ST],[ZIP],[Phone #] HAVING Count(*)>1 And Left([Company Name],5) = Left([Source].[Company Name],5) And Left([Address],7) = Left([source].[Address],7) And [City] = [source].[City] And [ST] = [source].[ST] And [ZIP] = [source].[ZIP] And [Phone #] = [source].[Phone #])))
ORDER BY source.[Company Name], source.Address, source.Suite, source.City, source.ST, source.ZIP, source.[Phone #];

[bigroo]

Hi jswenson3,

Does this give you the results you are looking for?