Results 1 to 4 of 4

Thread: Updating Tables

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Updating Tables

    I have a 56k row table comprised of the following (relevant) fields that was exported from a CRM program:

    PhoneNumber
    CustomerName
    Department
    Company
    City
    CustomerEmail

    Intent: To clean up the data prior to exporting it into a database used by our call center routing software. In its present form, the same phone number may be displayed in multiple records, each of which may differ by customer name, department, email, or all of the above.

    What I Need To Do:
    -Find duplicate phone numbers amongst the 56k list of numbers.
    -When duplicates are found, replace any existing values within the CustomerName, Department, and CustomerEmail fields with the word "Multiple". Do this for each duplicate record
    -Lastly, delete copies so that only one record (for one phone number) remains.

    Intended Result:
    When the call center software recognizes a phone number, it displays all relevant data that exists.
    If a phone number is recognized that, in the original table, had multiple CustomerNames, locations, etc., simply display what we're left with:
    PhoneNumber, Multiple, Multiple, Laboratory, Smithereen Inst., Houston.

    Does this smell like it needs to be coded, or is there a series of queries that I can perform?

    Thank you for your time.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd be using a series of queries. I only tend to code things that are going to be used quite often.

    Just curious, how do you intend to determine which details are the correct ones for a given set of multiple phone numbers?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    StarTrekker: I am slugging through a series of queries now; do you have any advice for deleting duplicates of records (where all fields are the same)?
    Regarding your question, I won't care; that's why I'm going to update(?) the other fields like CustomerName with some text like "many" or ">1" or something related. You're right: picking one caller (aka, CustomerName) when many people may have the same phone number seems arbitrary.
    The reason is that the call routing software only cares about the phone number, and when the agent answers the call, I'd rather display some bogus text ("many") than the wrong customer name.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by jdfilburn
    StarTrekker: I am slugging through a series of queries now; do you have any advice for deleting duplicates of records (where all fields are the same)?
    Yes. TAKE A BACKUP FIRST!!

    I think the first thing you have to do is a grouping query to count the number of records for a phone number. If there is only one entry for a number, return a 1. If there are two entries return a 2 etc. Then you can start manipulating the data so that you can treat the singles differently to the multiples.

    You could then do things like copy or make a table out of just the singles...

    What I would probably do since it's a one-off is to build another table from query results to build a unique table full of good data and replace the original table with it when complete.

    Next you could also append a distinct group query of the phone number specific fields (and expressions evaluating to "Multiple" for the customer specific fields) into that same table.... if you see what I mean?

    Just one approach.

    Quote Originally Posted by jdfilburn
    Regarding your question, I won't care; that's why I'm going to update(?) the other fields like CustomerName with some text like "many" or ">1" or something related. You're right: picking one caller (aka, CustomerName) when many people may have the same phone number seems arbitrary.
    The reason is that the call routing software only cares about the phone number, and when the agent answers the call, I'd rather display some bogus text ("many") than the wrong customer name.
    Ah... of course, thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •