With the Find function in Microsoft Access, the program will search up
and down a field for the value you input as the search string,
including leading and trailing spaces. This can be replicated by a
simple select query using the same string and the * before and after.
eg if searching for the value 'Myint' in a list of names, you would
simply set the criteria to
*" myint "*
the spaces indicating you want the word as a discreet whole and not
part of another (eg myinting)

I want to write a query which compares a list of surnames on the one
hand (a couple of hundred) with a list of full names, formatted in
many different ways on the other hand (around half a million)

Normal queries running two tables against each other would compare the
entire value of the field, and not look for the surname matches like
the Find function, or the one by one query. Obviously I could run a
couple of hundred separate queries to do this, but assume that there
is code that can be used to in effect generate a mass query checking
whether the surnames appear anywhere in the the list of half a million
full names.

If unsure of what I mean please ask for clarification

---

Request for Question Clarification by hammer-ga on 08 Jan 2003 04:42 PST

wolvies,

What would the result be? A list of surnames which do appear in the
list of full names? A list of surnames that do not appear? Something
else entirely?

- Hammer

---

Clarification of Question by wolvies-ga on 08 Jan 2003 05:19 PST

The resulting table (assuming it was a make-table query) would have
all the names that feature the surnames in it. That would be all from
the half million which feature any of the surnames (discreet string
elements) from the couple of hundred.

Wolvies

Hi, wolvies-ga:

Assume you have a Person table, with a text field called FullName. 
This is the table that contains half a million records to be searched
in your description.

Let's also assume the couple of hundred surnames are listed in another
table, called say Surname, with just a single text field "MyText".

Here is a query that returns the subset of records in the Person table
whose FullName field contains any of the Surname.MyText values as
substrings:

SELECT * FROM Person
WHERE EXISTS (SELECT * FROM Surname
  WHERE Person.FullName Like "*"&Surname.MyText&"*")

Note that Access's Like requires matching case in the specified
patterns.  Since the capitalization of names is sometimes variable,
you might want to make the query a little more robust by using the
LCase( ) function, which converts strings to all lowercase.  Thus:

SELECT * FROM Person
WHERE EXISTS (SELECT * FROM Surname
  WHERE LCase(Person.FullName) Like "*"&LCase(Surname.MyText)&"*")

Notice that after the EXISTS keyword in both queries, there follows a
"subquery" in parentheses which "depends" on the Person.FullName field
defined by the "outer" main query.

What the "EXISTS" keyword in SQL does results in a "true" value if
there are one or more records returned by the "dependent subquery, or
in a "false" value if there are no such records.  In these sample
statements the dependent subquery is designed to test whether the
FullName field given in the outer query contains any of the possible
matching surname strings.

regards, mathtalk-ga

---

Request for Answer Clarification by wolvies-ga on 12 Jan 2003 02:46 PST

Just a quick check - does this work in Access 97 ? Thanks

---

Clarification of Answer by mathtalk-ga on 12 Jan 2003 07:01 PST

Hi, wolvies:

Yes indeed. That's what I used to test and verify the syntax. 

regards, mathtalk-ga

---

Clarification of Answer by mathtalk-ga on 13 Jan 2003 08:49 PST

Thanks, wolvies, for taking the time to rate my answer.  It's very
appreciated, esp. as I have heard there may have been problems over
the past several days with that function.

best wishes, mathtalk

Thank you