You can achieve this by writing your SELECT statement with multiple conditions in the WHERE clause. If you are creating the search query in Access yourself, whether in Design or SQL View, you want to end up with something like this (in SQL View) --
select columns from yourtable where memofield like '*2001*' or memofield like '*Payroll*'
Just code as many conditions as you have keywords. Note the use of the asterisk as wildcard character; other databases use a percent sign.
Your next question is probably going to be "Well, how do I generalize that, so that I, or, more specifically, one of my users, can enter any number of keywords?"
How you deal with this will depend entirely on the language that you're using, and the way that you design the search interface. For example, you could offer multiple input fields, one per keyword, or else just one input field, like almost every popular search engine. In either case, you will have to write some code in a scripting language. If this is an Access application, you would write an Access module. If it's a Web page, you'll probably write some ASP or ColdFusion code.
What the script needs to do is figure out how many keywords were entered (by detecting which fields are empty, if you use more than one keyword field, or by detecting the blanks between keywords, if you use one field). The script will dynamically build the WHERE clause, concatenating an additional condition for each keyword after the first one with an OR. You could also have an option for "exact phrase" in which case you would insert the entire contents of the field into just one WHERE clause condition.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2003