How do I create a join on country codes that are part of phone number

[dkellygb]

I have a large taqble of telephone calls which consists of phone number dialled|time of day|Duration. I have a separate table which consists of Country code|Country Text|Rate per minute. I would like to create a join between these two tables so I can do some what if analysis with different rate structures. However the country code portion of the phone numbers range from 1 to 3 Characters. For example, USA is 1 and Ireland is 353, therefore a US number might be 0012125551212 while Ireland would be 003531232323. Is there anyway to create a join on variable lengths of characters or anyway to easily extract the country code?

Thanks in advance!

 

[Maquis]

Do you have to store the country code in the same field as the phone number? You could redesign your table to store these as 2 separate fields. Then the join would be easy.

You can always design queries and reports to display the 2 fields as 1 field later on, but you would probably need 2 textboxes on any forms that your users are using to key in the data. Maq B-)
<insert witty signature here>

 

[dkellygb]

The phone number and country code are in the same field. For example, an Irish phone number will look like this
00353123123123 where 00 is the international access code (always 2 chars), 353 is the country code (varies between 1 and 3 chars), and 123123123 is the phone number. My problem is breaking out the country code based on another tabel I have which lists all the country codes.

Dave

 

[BoxHead]

If you have all of the country codes broken out in another table, you should be able to compare your phone numbers to them. The key is that with any phone number, you have three possible string lengths that could be the Country Code.

Even though the Country Codes are different lengths, I have to assume they can't 'overlap'. Since the Country Code for America is 1, there can't be a two-digit or three-digit Country Code that begins with 1. If there was, as soon as you dialed the number 1 in the phone number, the call would be routed to America. Wouldn't it?

If this assumption is correct, you can build a query with your Telephone numbers in the first column and expressions in the next three.

Column1 is TNum
Column2 is OneDigitStr: Mid(TNum,3,1) 'skips the zeros.
Column3 is TwoDigitStr: Mid(TNum,3,2)
Column4 is ThreeDigitStr: Mid(TNum,3,3)

This will give you the three possible country codes from your telephone number. Only one can match a value in your country code table.

EG: Irelend number is 00353123123123
OneDigitStr = 3
TwoDigitStr = 35
ThreeDigitStr = 353

&quot;3&quot; or &quot;35&quot; cannot be country codes if &quot;353&quot; is.

Running another query against your Country/Country Code table with your criteria set to equal OneDigitStr OR TwoDigitStr OR ThreeDigitStr should result in only one match.

HTH

John