How to count number of times a city name appears in query in Acces

[Guest]

I need to report the number of times each city name appears in a query of
volunteers located around the country. Results should be one "City Name" and
the "number value" of times it appears for each city in the data base. I am
using MS Office Access 2003.

 

[Jeff Boyce]

Carla

Check Access HELP on the topic of Totals queries. I believe you could
create a new query, add the table (or query you already have), select the
[CityName] field and, say, the [WhateverYouCallTheRecordID] field. Click on
the Totals button in the toolbar.

Change the "GroupBy" on the [...ID] field to a "Count", and leave the
"GroupBy" on the [CityName].

Run it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi,

Be very careful here. Assuming that you are in the USA, every state has a
Springfield. There are many other city names used in multiple states.
Therefore you're going to also need to add State to the totals query and
group by it. The query would looks something like below.

SELECT Volunteers.City,
Volunteers.State,
Count(Volunteers.VolName) AS CountEachCityState
FROM Volunteers
GROUP BY Volunteers.City, Volunteers.State
ORDER BY Volunteers.City, Volunteers.State;

 

[John Spencer]

It gets even better. Some states have the same city (village, town) name
for more than one geographic entity. For instance, Maryland used to have to
entities named "Pekin". Eventually, the two got together and flipped a coin
and now we have Pekin and Nikep (reverse spelling).

 

[Jeff Boyce]

?What?! That's not logical. Who in their right minds would approve a
duplicate city na... oh, nevermind.

Jeff