MS Access: Wildcard Query run amok

April 27, 2007 7:31 AM

MS Access Question: I have two tables in a db file. One has a large list of single cell records and the other, a smaller list of records to be compared against the larger table. The issue is that I would like to use the values in the smaller table as wildcards to return like matches that are like the records in the larger table. Example follows.

The tables are like this.
---
Table 01: "Words"
puppy
concatenate
doggy
dog
cat
pup
house

Table 02: "Wild"
cat
dog
pup
----
An exact match of Wild to Words would yield:
cat
dog
pup

An unmatched search of Wild to Words would yield:
puppy
concatenate
doggy
house

What I would like is the Wild records (*cat*, *dog* and *pup*) to return:
puppy
concatentate
doggy
dog
cat
pup

Any thoughts on a quick lookup that will perform this search? My "Word" lists number in the millions and the "Wild" lists can be in the 100s of thousands. While this is certainly do-able on other platforms, I was hoping to contain it, for now in the Access world.

FWIW, I have an Excel solution, but it takes overnight to run the list comparisons.

Thanks!

J-

posted by lampshade to computers & internet (3 comments total)

The feature you need is LIKE. Simple version:

SELECT word FROM words WHERE word LIKE '%dog%'

I first reached for subselects, but they don't work with LIKE if the inner query returns more than one row. Sooooo...

UPDATE wilds SET wild = CONCAT('%', wild, '%')

SELECT DISTINCT word FROM words INNER JOIN wilds WHERE word LIKE wild

A more subtle solution wouldn't alter the data in the wilds table, but I'm lazy.

I did this on MySQL, so you may need to make slight accent changes, but the approach is clear.
posted by Leon at 8:08 AM on April 27, 2007

BTW, with that much data I'd store the results of the query in a joining table and then update it occasionally.
posted by Leon at 8:17 AM on April 27, 2007

While not directly answering the question, my q from last month might give you some inspiration on searching options, and does somewhat focus on the timing of running a list comparison query.
posted by langeNU at 9:09 AM on April 27, 2007