Counting instances of a string in a memo field

[Keith]

Access 2000

I have inherited a db where the entire address info is
stored in a memo field (street, suburb, city). :-(

I want to be able to count the number of clients in a list
of cities. I have figured out how to do a single city:

SELECT Count(declearer.Postal) AS CountOfPostal
FROM declearer
WHERE (((declearer.Postal) Like "*auckland*"));

but how would I go about a set of cities

TIA

 

[John Spencer (MVP)]

Not easily if you have that structure. A Union Query may allow you to do several
at once, but that will run into its limits pretty quickly.

SELECT "AuckLand" as City, Count(declearer.Postal) AS CountOfPostal
FROM declearer
WHERE declearer.Postal Like "*auckland*"
GROUP BY City
UNION ALL
SELECT "Candy" as City, Count(declearer.Postal) AS CountOfPostal
FROM declearer
WHERE declearer.Postal Like "*Candy*"
GROUP BY City
UNION ALL
....


UNTESTED IDEA. Build a table of your City Names and then try something like

SELECT Cities.CityName, Count(Declearer.Postal) as CountPostal
FROM Declearer INNER JOIN Cities
On Declearer.Postal LIKE "*" & Cities.CityName & "*"
GROUP BY Cities.CityName

You obviously cannot build this query using the query grid.

This will probably work except in those cases where a city name is contained
within another city name or the city name is in the street address. In that
case you would get some false counts. For instance, here in Maryland we have
the city Baltimore, and my sister lives in another city on Baltimore Avenue.

 

[Keith Rodgers]

This will probably work except in those cases where a city name is contained
within another city name or the city name is in the street address. In that
case you would get some false counts. For instance, here in Maryland we have
the city Baltimore, and my sister lives in another city on Baltimore Avenue.

Thanks John

I'll try the stuff you suggested. I've already struck the problem you've
mentioned above. There is a Mt Wellington in Auckland and Wellington
(city) is one of the cities I want to count.

Maybe I'll have to bite the bullet and get the tables normalised :-(