group by area code help 2

[patrichek]

Hi Guys,
I'm trying to build a query where the results are a count of our contacts by area code.

Question 1

I want a count of the area code results and show only the area code in my query.

here is my code:

SELECT Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, IIf([homephone],Null,[Mobilephone]) AS AltPhone
FROM Contacts
GROUP BY Contacts.City, Contacts.StateOrProvince, Contacts.HomePhone, IIf([homephone],Null,[Mobilephone])
HAVING (((Contacts.StateOrProvince)="CA"))
ORDER BY IIf([homephone],Null,[Mobilephone]);

I'll ask question 2 later.

thanks!

 

[HarleyQuinn]

I wasn't sure what was the areacode in your query so I guessed (probably incorrectly) at StateOrProvince.

A very simple version of counting the contacts per area in your contacts table would be:

SELECT Contacts.StateOrProvince, Count(*)
FROM Contacts
GROUP BY Contacts.StateOrProvince;

Is this something like what you're after?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AlexCuse]

I think you want to remove your group by city, because you will probably have multiple cities in each area code. This query assumes your area code is the left three characters of phone number (no parentheses). You can adjust it accordingly of course.

select left(IIf([homephone],Null,[Mobilephone]), 3) as AreaCode
, StateOrProvince
, count(StateOrProvince)
from Contacts
group by left(IIf([homephone],Null,[Mobilephone]), 3), StateOrProvince

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fneily]

I'll assume your area code is embedded in homephone. You could in your query use the Left or Mid function to extract the area code then sort and count on that new field.
Also, this is a good example that I use in my classes about normalization. The first form states you break fields down into the lowest possible denomination. The phone number is interesting because only recently in history do you include the area code in stating your home phone number. So do you keep it as one field? Or do you have two fields - one for area code and one for the number? In your case, you should have created your table with two fields.

 

[patrichek]

Alex, that's it!

unfortunately my records are mix and match (XXX)-XXX-XXXX and XXX-XXX-XXXX. Is there any way to incorporate if "(" exist then Left([HomePhone],3) if "(" does not exist then Left([HomePhone],5)?

thanks!

 

[patrichek]

Good point fneily

 

[HarleyQuinn]

Now I actually understand the question (), how about something like (building on Alex's code):

select IIf(instr([homephone],"("),mid([homephone],2,3),left([homephone],3)), 3) as AreaCode
, StateOrProvince
, count(StateOrProvince)
from Contacts
group by IIf(instr([homephone],"("),mid([homephone],2,3),left([homephone],3)), 3) as AreaCode, StateOrProvince

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[patrichek]

HarleyQ,
sorry, my weakness has always been explaining my questions

I'm getting the error "Extra ) in query expression '3)'
its highlighting the ")" before "as AreaCode"
If i remove the ) then i get a new error "missing operator "before as Areacode.

any ideas?

 

[HarleyQuinn]

Take off the ",3)" (that used to be part of the Left function and I left in from Alex's post) from the end of the line of code and it should work fine.

Sorry about that!

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AlexCuse]

Patrick -

I am thinking one more thing about your query. Don't you want to have all your area codes in one column, and if so wouldn't this be your best bet:

select left(IIf([homephone] [b]is null[/b],
replace([Mobilephone], '(', ''), 
replace([homephone], '(', '')), 3) as AreaCode
, StateOrProvince
, count(StateOrProvince)
from Contacts
group by left(IIf([homephone] is null,
replace([Mobilephone], '(', ''), 
replace([homephone], '(', '')), 3), StateOrProvince

(typed, hope I got the parentheses right)

I chose to use replace simply to avoid nesting IIF statements, I hate to do that

Ignorance of certain subjects is a great part of wisdom

 

[patrichek]

Ok, got it working sort of

Here is what I need now:

1.shows area codes in California in Areacod1 field (working)
2.if homephone is null then shows mobilephone as Altphone (working), can I somehow add this to the AreaCod1 results?
3. I need it to group the AreaCod1 results and give a count of each group.

example:

AreaCod1 Total

(310) 45
(805) 7
(323) 14

Thanks!

 

[patrichek]

sorry, my current code:

[code}
SELECT IIf(InStr([homephone],"("),Mid([homephone],2,3),Left([homephone],3)) AS AreaCod1, Contacts.HomePhone, Contacts.StateOrProvince, IIf([homephone],Null,[Mobilephone]) AS AltPhone
FROM Contacts
WHERE (((Contacts.StateOrProvince)="CA"))
ORDER BY IIf(InStr([homephone],"("),Mid([homephone],2,3),Left([homephone],3));

[/code]

 

[AlexCuse]

HAHAHA that is just what I was thinking you might want to do. See my post above, I think it will help you out.

Ignorance of certain subjects is a great part of wisdom

 

[patrichek]

once again, as it did not post all of it in the previous post.

SELECT IIf(InStr([homephone],"("),Mid([homephone],2,3),Left([homephone],3)) AS AreaCod1, Contacts.HomePhone, Contacts.StateOrProvince, IIf([homephone],Null,[Mobilephone]) AS AltPhone
FROM Contacts
WHERE (((Contacts.StateOrProvince)="CA"))
ORDER BY IIf(InStr([homephone],"("),Mid([homephone],2,3),Left([homephone],3));

 

[AlexCuse]

Patrick -

Did you see my post at 10:17? I think this will do what you want, you just will need to add where clause and order by (and remove StateOrProvince from the select)

Post back if you have any problems with it,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[patrichek]

Hi Guys,
what i did was union query my phone numbers then adjusted the areacode of calif. query then i made a third query to group and count the results.

thanks to all!

 

[patrichek]

union query

SELECT Contacts.StateOrProvince, Contacts.WorkPhone
FROM Contacts
WHERE (((Contacts.StateOrProvince)="ca") AND ((Contacts.WorkPhone) Is Not Null))
Union All
SELECT Contacts.StateOrProvince, Contacts.HomePhone
FROM Contacts
WHERE (((Contacts.StateOrProvince)="ca") AND ((Contacts.HomePhone) Is Not Null))
UNION ALL SELECT Contacts.StateOrProvince, Contacts.MobilePhone
FROM Contacts
WHERE (((Contacts.StateOrProvince)="ca") AND ((Contacts.MobilePhone) Is Not Null));

Calif. Area code query:

SELECT IIf(InStr([workphone],"("),Mid([workphone],2,3),Left([workphone],3)) AS AreaCod1, CAPhoneNumberUnionQRY.WorkPhone
FROM CAPhoneNumberUnionQRY
GROUP BY IIf(InStr([workphone],"("),Mid([workphone],2,3),Left([workphone],3)), CAPhoneNumberUnionQRY.WorkPhone
ORDER BY IIf(InStr([workphone],"("),Mid([workphone],2,3),Left([workphone],3));

group and count query:

SELECT CAAreaCodesQRY.AreaCod1, Count(CAAreaCodesQRY.AreaCod1) AS CountOfAreaCod1
FROM CAAreaCodesQRY
GROUP BY CAAreaCodesQRY.AreaCod1
ORDER BY Count(CAAreaCodesQRY.AreaCod1);

thanks again