Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

The SQL Guru Answers your Questions...

---

Today's question comes from Paul S.:

How can I easily search all fields in a row. i.e. I want to offer one field on my form to use as a search field and for it to check all fields in the resulting rows. At present to do this I string all the fields together and do a STRINGFIELD LIKE '%searchtext%' but this isn't very pretty or efficient. (I'm using an Access 97 database)

Paul,

I'm assuming you're trying to search from a SQL Query.

There are a couple of different options for doing this, though by using Access, you're sort of limited. You've already named one of them, and I agree, it isn't pretty. How about something like the following:

SELECT * FROM Foo WHERE col1 LIKE '%search%' OR col2 LIKE '%search%' OR col3 LIKE '%search%'

Unfortunately, OR queries tend to be pretty slow. Depending on the size of your Access table and how you index the columns, this may work okay. (Though I'm not sure Access can use an index when you have wildcards both before and after the search word... SQL Server can't.)

If you were using SQL Server 7.0, I'd just suggest a full text index. Since you're using Access 97, you can fake it, in a way... (You may want to check to see if Access2000 support full text indexing, last time I checked, it did not).

You can create a Keyword table (KeywordID, SearchString) that contains distinct strings or words that you've pulled from the table(s) you want to search. Join the KeywordID to the original table(s) by way of an associative (many-many) table. In your query, put your WHERE clause criteria on the Keyword.SearchString column, then join the returned rows (through the associative table) back to the original table rows.

This should work pretty well, provided of course you index the SearchString column. However, keeping the Keyword table up to date can be a pain, especially if your data is pretty volatile. I'd try the multiple OR conditions first.

Good Luck!
Sean

---

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article