Searching for Text Strings in a Memo Field

[Andrew]

I am building a database in Access 2003 that will filter approximately
65,000 records of bank transactions and flag those transactions that contain
the names of high risk countries (and other key words). I have a query with
a calculated field that aggregates the text from all fields within each
transaction message into a single field called AggregateText, and a table
with a list of countries and their risk scores.

I am currently running a second query which combines the query
qryAggregateText and the table tblGeoSearchStrings using the below SQL
statement:

INSERT INTO tblGeoHits ( TxnID, SearchString, GeoID )
SELECT qryAggregateText.TxnID, tblGeoSearchStrings.SearchString,
tblGeoSearchStrings.GeoID
FROM qryAggregateText LEFT JOIN tblGeoSearchStrings ON
qryAggregateText.AggregateText Like "*" & tblGeoSearchStrings.SearchString &
"*";

As you can see, I am building a table to store the results of the query,
which searches for country names (tblGeoSearchStrings.SearchString) in each
transaction message (qryAggregateText.AggregateText). The AggregateText
field is a calculated field created by combining several text fields. It
often contains more than 255 characters.

What I would like to do is store the contents of AggregateText in a memo
field
in a table and run the query against that memo field, but when I try
replacing the AggregateText query with an AggregateText table, the query no
longer works because I cannot join the memo field in tblAggregateText to the
text field tblGeoSearchStrings.SearchString . How can I modify this query so
that it will work with a memo field?

Alternatively, is there a more efficient way of writing this query, as the
current query runs extremely slow?

Thanks for any advice!

 

[Douglas J. Steele]

Try:

INSERT INTO tblGeoHits ( TxnID, SearchString, GeoID )
SELECT qryAggregateText.TxnID, tblGeoSearchStrings.SearchString,
tblGeoSearchStrings.GeoID
FROM qryAggregateText, tblGeoSearchStrings
WHERE qryAggregateText.AggregateText Like "*" &
tblGeoSearchStrings.SearchString & "*"

A through-back to an older syntax, but it might work in this case.

Note that this is now the equivalent of an INNER JOIN.

 

[Andrew]

Thanks, that seems to have fixed the problem. I am now using the SQL
statement below, except instead of using qryAggregateText, I am pulling my
AggregateText memo field from tblAggregateText. The query now runs a bit
faster, but is still slow.

Thanks for your help!