Complex Query in Access

[Huyeote]

Hi, there, I have a Access table which contains hundreds of thousands of
call logs. The data fields useful in this case is phone number (area code +
phone no) and duration. And I have another table which records all billable
area codes (prefix). I need to summarise total of minutes called to every
single area code from the call details table. Instead of query one area code
every time in a simple query, can I query minutes to all prefix ( I have
more than 500 prefix) in just one go? Any help will be appreciated.

Thanks.

Regards,

Huyeote

 

[Arvi Laanemets]

Hi

On fly:

Without using area codes table (area codes must be all fixed length, p.e. 3
characters, and you get only areas with calls):
Select Left(PhoneNumber,3) As Area, SUM(Duration) As Duration From
CallTable Group By Left(PhoneNumber,3) Order By 1

Using area codes table:
Select b.Area, SUM(a.Duration) From CallTable a, AreaTable b Where
Left(a.PhoneNumber,Len(b.Area))=b.Area Group By b.Area Order By 1


Arvi Laanemets

 

[John Vinson]

Hi, there, I have a Access table which contains hundreds of thousands of
call logs. The data fields useful in this case is phone number (area code +
phone no) and duration. And I have another table which records all billable
area codes (prefix). I need to summarise total of minutes called to every
single area code from the call details table. Instead of query one area code
every time in a simple query, can I query minutes to all prefix ( I have
more than 500 prefix) in just one go? Any help will be appreciated.

Yes, you can, but since apparently you have the area code embedded in
the phone number instead of as a separate (indexed) field, it will be
very slow and inefficient.

Two ways to try, making guesses at your fieldnames which you'll need
to correct:

1. SELECT BillableCodes.AreaCode, Sum(Calls.Calltime) AS SumOfTime
FROM Calls, BillableCodes
WHERE Calls.Phone LIKE BillableCodes.Code & "*"
GROUP BY BillableCodes.AreaCode;

2. a Non Equi Join:

SELECT BillableCodes.AreaCode, Sum(Calls.Calltime) AS SumOfTime
FROM Calls INNER JOIN BillableCodes
ON Calls.Phone LIKE BillableCodes.Code & "*"
GROUP BY BillableCodes.AreaCode;

 

[Huyeote]

Thanks for the query, it does work. But the grand total minutes of the equey
is larger than what I got if I only query durations. The problem is due to
overlap of some prefix like 21 & 217. Minutes called to 217 is also included
in the minutes called to 21. How can I solve this problem?

Hueyote

 

[Huyeote]

Thanks for the query, it's simpler than I thought. But the grand total
minutes of the equey is larger than what I got if I only query durations.
The problem is due to overlap of some prefix like 21 & 217. Minutes called
to 217 is also included in the minutes called to 21. How can I solve this
problem?

 

[Arvi Laanemets]

Hi


I'm afraid you have a serious problem there due your earlier database
design. As I understand, area codes can be different length. When phone
numbers also can have different length, the only option you have left, is
manually sort them between areas. When ALL phone numbers (without area code)
are same length, then you can try something like:
Select b.Area, SUM(a.Duration) From CallTable a, AreaTable b Where
Left(a.PhoneNumber,Len(a.PhoneNumber)-LengthOfPhoneNumber)=b.Area Group By
b.Area Order By 1


Arvi Laanemets

 

[John Vinson]

Thanks for the query, it's simpler than I thought. But the grand total
minutes of the equey is larger than what I got if I only query durations.
The problem is due to overlap of some prefix like 21 & 217. Minutes called
to 217 is also included in the minutes called to 21. How can I solve this
problem?

Hrm. I thought that Area Codes were all three digits. What is a 21
prefix!?

This will be MUCH MUCH more difficult, since if you have a Phone field
containing

2174444444

there is no obvious way to tell whether that's a 21 or a 217 code.

 

[Huyeote]

Sorry, I should tell you the phone number string has country code as well as
area code. I just made an example using dummy figure like 21 & 217. To be
more specific, we can use 66 which is the country code for Thailand, and 662
(66 + area code 2) for Bangkok. Now I know there is no simple way to do it
in SQL. Thanks any way.

Huyeote

 

[John Vinson]

Sorry, I should tell you the phone number string has country code as well as
area code. I just made an example using dummy figure like 21 & 217. To be
more specific, we can use 66 which is the country code for Thailand, and 662
(66 + area code 2) for Bangkok. Now I know there is no simple way to do it
in SQL.

If you don't have the country codes and the area codes distinguished
in SOME way, then I cannot imagine any way to do it in SQL *or any
other way*. For instance, is 6032564113 a number in the US in area
code 603, or Malaysia 60+Kuala Lumpur 3? How would you tell?