Another wild guess! Have you used the 'lookup wizard' for the data type
when creating the field in the table in question? If so, although you see
text in the field, its data type will in fact be a number which references a
numeric primary key of a separate 'Cities' table. If so you'll need to join
the tables on the two numeric fields, and then include the text field from
the 'referenced' Cities table and enter the city name as the criterion for
this column. In SQL your query would look something like this:
SELECT CityName, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName = "New York"
GROUP BY CityName;
where YourTable is the current 'referencing' table, cities is the
'Referenced' table with one row per city, CityID is the name of the two
fields on which the tables are joined, and CityName is the name of the text
field in the Cities table.
If you want to include more than one city in the criteria you can use the IN
operator:
SELECT CityName, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName IN("New York", "San Francisco", "Chicago")
GROUP BY CityName;
Bear in mind that city names can be duplicated, so you could be counting two
cities as one. This is one reason why you should use a numeric value as the
keys as there can then be more than one row in Cities with the same CityName
but different numeric CityID values. You can then group a query on the
CityID as well as CityName columns to distinguish them, in which case you'd
probably also include a field such as State from the Cities table in the
result table:
SELECT Cities.CityID, CityName, State, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName IN("New York", "San Francisco", "Chicago")
GROUP BY Cities.CityID, CityName, State;
BTW if you have used the 'lookup wizard' se the following for reasons not to:
http://www.mvps.org/access/lookupfields.htm
Ken Sheridan
Stafford, England
