Results 1 to 13 of 13
  1. #1
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7

    Help dealing with duplicate records

    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??

  2. #2
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    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??
    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

  3. #3
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    Quote Originally Posted by bigroo View Post
    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.

  4. #4
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Help dealing with duplicate records

    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!!

  5. #5
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    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!!

  6. #6
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Help dealing with duplicate records

    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??

  7. #7
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    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.

  8. #8
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Help dealing with duplicate records

    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?

  9. #9
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    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.

  10. #10
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Help dealing with duplicate records

    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!!

  11. #11
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    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!

  12. #12
    jswenson3 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    7
    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 #];

  13. #13
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Help dealing with duplicate records

    Hi jswenson3,

    Does this give you the results you are looking for?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query will duplicate records
    By funkygoorilla in forum Queries
    Replies: 3
    Last Post: 09-29-2011, 01:32 AM
  2. preventing duplicate records
    By Alliana Gray in forum Access
    Replies: 6
    Last Post: 08-11-2011, 01:18 PM
  3. Duplicate records in Form view
    By Reh in forum Database Design
    Replies: 7
    Last Post: 08-10-2011, 07:21 AM
  4. Duplicate Records
    By softspoken in forum Queries
    Replies: 3
    Last Post: 06-21-2010, 03:33 PM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums