Source of data: imported Contacts records from company's CRM system (via Excel)
Data contains typical Contact-related info: PhoneNumber (cANI), contact name, contact's company name, his/her location (ie, City, State, Country), contact's company status (Platinum, Null, etc.). all fields are in one table
Purposes of using Access: to clean up the phone numbers and to resolve duplicates.
What I've done so far: 'cleaned' the phone numbers by removing non-numeric characters and spaces. moved 'simple' cases (one phone number and only one contact) to a 'good' table.
Where I'm stuck: the records remaining in the source table have multiple records for each phone number due to the fact that one of the associated fields differs from the rest (within the group).
What I want to happen: I want to end up with one record per cANI (phone number), as the final data set will be used as a lookup table for our phone system.
My "plain English" logic:
Real-World application:
(pre-treated data)
Notice that either Abe or Carol could be calling from 8675309, but we do know that the call originates from Acme, but not from which location. So the following one-liner is created for the lookup table:
(post-treated data)
Thanks in advance for any help or guidance you can provide!
Data contains typical Contact-related info: PhoneNumber (cANI), contact name, contact's company name, his/her location (ie, City, State, Country), contact's company status (Platinum, Null, etc.). all fields are in one table
Purposes of using Access: to clean up the phone numbers and to resolve duplicates.
What I've done so far: 'cleaned' the phone numbers by removing non-numeric characters and spaces. moved 'simple' cases (one phone number and only one contact) to a 'good' table.
Where I'm stuck: the records remaining in the source table have multiple records for each phone number due to the fact that one of the associated fields differs from the rest (within the group).
What I want to happen: I want to end up with one record per cANI (phone number), as the final data set will be used as a lookup table for our phone system.
My "plain English" logic:
Group records by cANI
Evaluate each field within the group (cContactName, cCompanyName, etc.)
IF count of distinct values in other field = 1, then preserve that value
IF count of distinct values in other field > 1, then replace those values with a "?"
After re-grouping (perhaps? or record transfer to 'good' table?), there will be one phone number followed by fields with real values and possible others with just a ?Real-World application:
(pre-treated data)
PhoneNumber ContactName CompanyName CityName
8675309...........Abe................Acme................Houston
8675309...........Carol..............Acme................Chicago
Notice that either Abe or Carol could be calling from 8675309, but we do know that the call originates from Acme, but not from which location. So the following one-liner is created for the lookup table:
(post-treated data)
8675309.............?..................Acme..................?
Thanks in advance for any help or guidance you can provide!
