Query To Look For Specific Words within a Large Database?

[cmf0106]

I have a very large database where I need to pull out specific words. Based on the example database below, if I wanted to run a query that looked for the specific word "black" or "red", it would look through the database and give an output that had every database entry that contained the word "black" or "red".

Black car
Green apple
Black cobra
Yellow Sun
Red Shirt

The output for looking for the word "black" or "red shirt" would be: black car, black cobra, red shirt

Is there anyway to accomplish this in access? I will need to do this for about 50 specific words, so quite a few.

[orange]

Are you really using Access 97?

[cmf0106]

Are you really using Access 97?

No sorry, that should be 2007, let me adjust that.

[NTC]

yes; in the query criteria use the wild card symbol *; as in "black*"

[cmf0106]

yes; in the query criteria use the wild card symbol *; as in "black*"

How do I scale this if I need to do it for a large number of words (50+) in a large database (over 60,000)?

[orange]

I have a routine that will search all text fields in all tables in the database for strings. Tables must have a single field Primary Key- which can be in any position. I can attach it as an mdb.
You could link your tables to this database.
It is not suited to the volumes you're requesting (50 strings), but it does search all text fields in all tables.
Feedback welcome.
Good luck

[John_G]

Do you want to look for all of these words in one query?

Its easy enough in query design view. In the criteria area for the field you want to search on, put each of the words you are looking for on a separate line, surrounded by *, like this: *Red*. This can get a tricky, because *red* will find not only the word Red, but all occurrances of the string, as in Freddy.

If you only want the word, use blanks as well, as in * red *. But that has its own problems, because it won't find Red at the beginning or end of the field, nor will it find things like Red's or red, . You can get around the beginning or end part by adding blanks as part of the where clause. In SQL, it would look like this:

SELECT Text_ID, Text_En, Text_Fr FROM Statement_Text
WHERE " " & [Text_En] & " " Like "* monitor *"

You see where this is going I think - it is not quite as easy as you might think.

I just tried this in A2003, and it won't let me use more than 9 criteria (in query design view), and running that on a table of 160,000 records took about 10 seconds.

HTH

John

[ItsMe]

...and it won't let me use more than 9 criteria...

I ran into a criteria limit using VBA in Access 2010 just the other week. Not sure what the magic number was but I was able to use many many AND and OR operators in a WHERE clause. It may have been a character limit in the SQL statement string I was concatenating the WHERE clause to.

[NTC]

the correct answer/design depends on how your data is structured and the user experience you seek; your original post is showing a single column - and that structure is very search-able. 60k records is not large by database standards...

if it is 60 different single criteria i.e. black* or red* and you want the user to make it up as they go you can consider a 'parameter prompt' - that's the term for a query design - bing that - - and the user will get a prompt just before the query runs. it is easy to set up

a more elegant design is generically referred to a query by form - where you set up a form and with either a text box or combobox the user selects the items to search for... it can take a bit of coding for multiple criteria that have either OR or AND (typically a check box to designate which) - particularly if the user is free to do anything they want and the developer must anticipate any scenario...

however when there is a high volume of criteria - - - it becomes more manageable to approach it completely differently; rather than use criteria in a single query - instead use a join between 2 queries. The first query is the query that holds all the terms that are criteria - and it joins to the table (or query) that holds the data. In this approach one cannot use wild cards but you can have unlimited criteria.