Count how many times and sort ?

[SpookiePower]

I have a table TReport which have a field called vcHeader.
In vcHeader is mention 3 different city names and some other text.
I now want to count how many time each city is in the table
and sort by how many times they are mention.

I know how to search for one city, like this -

SELECT count(*) AS Cities
FROM TReport
WHERE vcHeader Like '*NameOfCity*';

and a result like this -

Cities HowManyTimes
A 99
B 66
C 33


But I'm all out of ideas, how to do it.

 

[Michel Walsh]

SELECT city, COUNT(*)
FROM treport
WHERE vcHeader LIKE "*NameOfCity*"
GROUP BY city
ODER BY city



The WHERE clause can be removed, if you want the count for all the cities in
the table treport.



Hoping it may help,
Vanderghast, Access MVP

 

[SpookiePower]

SELECT city, COUNT(*)
FROM treport
WHERE vcHeader LIKE "*NameOfCity*"
GROUP BY city
ODER BY city

The WHERE clause can be removed, if you want the count for all the cities in
the table treport.

Hoping it may help,
Vanderghast, Access MVP

I'm not sure. Because I have to search for the name of the city
among some other text in the vcHeader textfield.