most frquent substring

[vjp2.at]

If I have a list of phone numbers by district and I want the most frequent
exchange (forst six of ten digits) for each district, what is the syntax that
would work with MS ACCESS 2007?



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]

 

[Bob Barrows]

If I have a list of phone numbers by district and I want the most
frequent exchange (forst six of ten digits) for each district, what
is the syntax that would work with MS ACCESS 2007?

Select top 1 left(phonenum,6) as exchange, count(*) as total
from table
group by left(phonenum,6)
order by count(*) desc

 

[vjp2.at]

But my problem is that this gives me the most freq exchange for everything,
that than for each region (ED,AD). I tried pivoting it (AD, vs ED, with
MAX(COUNT) but it didn't work)

SELECT top 1 Left([qvot12].[Phone],7) AS FonXcg
FROM qvot12
GROUP BY qvot12.AD, qvot12.ED,Left([qvot12].[Phone],7)
HAVING Not IsEmpty("Phone")
ORDER BY Count(Left([qvot12].[Phone],7)) DESC;


- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.panix.com/~vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos]

 

[Bob Barrows]

But my problem is that this gives me the most freq exchange for
everything, that than for each region (ED,AD). I tried pivoting it
(AD, vs ED, with MAX(COUNT) but it didn't work)

So ED and AD are field names? Two fields are needed to designate a region?
OK ...

What follows is not a pivot, it's a revised group by statement.

SELECT top 1 Left([qvot12].[Phone],7) AS FonXcg
FROM qvot12
GROUP BY qvot12.AD, qvot12.ED,Left([qvot12].[Phone],7)
HAVING Not IsEmpty("Phone")
ORDER BY Count(Left([qvot12].[Phone],7)) DESC;

select AD, ED, Left([Phone],7) AS FonXcg
,Count(*)
FROM qvot12
WHERE Phone is not null
GROUP BY AD, ED,Left([Phone],7)
ORDER BY Count(*) DESC;

If this fails to give you what you want, you will need to provide a few rows
of sample data in tabular format, followed by the desired results from that
sample data, also in tabular format.