Most Frequent string by region

[vjp2.at]

How would i get this to work? It complains baout not including the
aggregate in the result..

SELECT tract, region, Left([Phone],7) as FonXcg
FROM db1
Group by region
Order by Count(FonXcg) desc limit 1
Pivot by tract;



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://www.facebook.com/vasjpan2
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]

 

[John Spencer]

Perhaps you could describe what you are attempting to do with the query.

Your syntax is in error.

Perhaps you want the following?

SELECT tract, region, Left([Phone],7) as FonXcg
FROM db1
Group by Tract, region, Left([Phone],7)
Order by Count(Left([Phone],7)) desc

You had a Pivot clause in there which would suggest you were attempting to use
a crosstab query, but there was no Transform clause.

Also, what is Limit 1 supposed to accomplish. Were you trying to return just
the TOP 1 record

SELECT TOP 1 tract, ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Vasos Panagiotopoulos]

Microsoft Jet engine cannot find T1..??