SQL Statements?

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

I have a large problem. I have a database of 2500 records
in Access (we are moving the records from ACT into our
new MS CRM system). The problem is that all of the phone
numbers have been entered in a variety of different ways.
I need to standardise them and was told that if I wrote
some SQL statements that I could do this.

Needless to say I have absolutely no idea how to do this
and any assistance would be greatly appreciated or I am
looking at manually updating 2500 records.

Thanks
 
Suzanne

Depending on how the phone numbers were created, you might be able to write
a few queries. However...

Can you indicate what the variety is?
Are these just USA or are there international phone numbers?
Are there extensions applied to the phone numbers?
Is there area code included?

My immediate thought is that you would have to process each record at a
time, for the following reasons:
1. First strip out all non-numeric characters, just so that you have a
plain, vanilla phone number.
- This eliminates distinction between (703) 555-1212, 703-555-1212,
7035551212

2. If you are dealing with USA phone numbers, then you can check if the
area code was provided.
This does not work if the phone number includes extensions. Extensions
should be their own field.
- If Len(PhoneNumber) = 10 then you have area code included.
- If Len(PhoneNumber) <> 10, then you were not provided an area code.

3. I'm not sure how you would want to format the phone numbers so this is
where you will need to
do some manipulation.
- If Len(PhoneNumber)=10 then -> format it like: (###) ###-####
- If Len(PhoneNumber)<> 10 then -> format it like: (000) ###-####

Hopefully this will give you some food for thought...

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top