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

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



"Huyeote" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>

On Mon, 4 Aug 2003 14:52:19 +1000, "Huyeote" <(E-Mail Removed)>
wrote:

>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;


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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


"Arvi Laanemets" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "Huyeote" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
> >
>
>

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?



"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Mon, 4 Aug 2003 14:52:19 +1000, "Huyeote" <(E-Mail Removed)>
> wrote:
>
> >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;
>
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public
>

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


"Arvi Laanemets" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "Huyeote" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
> >
>
>

On Mon, 4 Aug 2003 16:29:48 +1000, "Huyeote" <(E-Mail Removed)>
wrote:

>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.


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Mon, 4 Aug 2003 16:29:48 +1000, "Huyeote" <(E-Mail Removed)>
> wrote:
>
> >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.
>
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public

On Tue, 5 Aug 2003 12:03:55 +1000, "Huyeote" <(E-Mail Removed)>
wrote:

>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?


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public