Re: Counting instances of a string in a memo field

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 02/02/05 

Date: Tue, 01 Feb 2005 19:18:05 -0500

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 wrote:
>
> 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

Relevant Pages

  • Re: Queries when the "one" table is used several times
    ... When you create a query into this table, the problem will be with the joins. ... When you add the Cities table, Access might produce 5 lines between Cities ... there is no city. ... By making outer joins like this, the query returns all records from your ...
    (microsoft.public.access.queries)
  • adding a new record in a form using a VBA procedure
    ... table contains potential sales leads. ... specifies which city the lead is in. ... The list of allowable cities is stored ... The form's data source is a query of the sales leads table. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Need to tune a table for performance gains
    ... as long as you know what city a person is in. ... query to find out how many records that is. ... in when you are filtering on several columns. ... We create two _Mini ...
    (comp.databases.ms-sqlserver)
  • Re: Sorting a concatenated query problem
    ... Make sure the query has the City field in it as a standalone field. ... Bob, by View menu item, do you mean the green report icon with view ... StackedLastMidFirstAddressCITYALPHA, it does not provide the same ...
    (microsoft.public.access.gettingstarted)
  • Re: Why?
    ... I have to look at my query that couldn't pass the 8.0/9.0 test. ... Select Sum, City, State from Invoices Group By State ... (Yeah, lame example as queries go, but look at your non-compliant ...
    (microsoft.public.fox.programmer.exchange)