Query to find records containing all words

[Don Hyams]

Query to find records containing all words

I have a rather sloppy freeform Access 97 table that was imported from
an old DOS-based database. The information is haphazardly spread
among different fields in each record. Unfortunately, optimizing it
(cleaning it up) would be a insanely huge project. This means that
any searches must check all fields in each record.

Here's a simplified version of the current query where the criteria is
entered in a textbox on a form:

SELECT DISTINCTROW tblData.Field1, tblData.Field2, tblData.Field3,
tblData.Field4
FROM tblData
WHERE (((tblData.Field1) Like "*" & [Forms]![frmSearch]![txtCriteria]
& "*")) OR (((tblData.Field2) Like "*" &
[Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field3) Like
"*" & [Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field4)
Like "*" & [Forms]![frmSearch]![txtCriteria] & "*"))
ORDER BY tblData.Field1

This works fine if only one word is in the search criteria. But if
multiple words are entered, they are treated as a phrase.

I need to be able to query all of the fields in each record in this
table and return records that contain multiple criteria. In effect,
treating each record as one long field.

For example, if the user types "George" in a search field, the query
should return all records that have "George" in ANY field.

If the user types "George Washington", the query should return all
records that have"George" in any field AND "Washington" in any field.
Any record that did not contain both words (looking through all
fields) would not be returned.

If the user types "George Washington Carver", the query should return
all records that have"George" in any field AND "Washington" in any
field AND "Carver" in any field. Any record that did not contain all
three words (looking through all fields) would not be returned.

ETC, ETC...

I realize I'll probably have to limit the number of words that can be
used in a query in this manner, although it would be nice not to have
that restriction.

As part of the solution I presume I'll have to have multiple keyword
search fields or set up some kind of parsing on one search field.

Surely this isn't a unique problem. Thanks in advance for helping me
avoid reinventing the wheel.

--Don

 

[Dale Fye]

Don,

I've got done something similiar in the past and here is a quick
explaination of how I did it, this may not be the most efficient.

1. Create a WordSearch table that only contains on field (KeyWord,
text). When you hit the command button to execute your search, parse
your [Forms]![frmSearch]![txtCriteria] field using split and insert
each of the individual words into this table (don't forget to delete
all the previous records first).

2. Create a union query that collects all the text fields you are
interested in into a single virtual table. Save this query as
qry_Union.

SELECT T.ID, 1 as FieldNo, T.Field1 as SearchIn From yourTable T
UNION
SELECT T.ID, 2 as FieldNo, T.Field2 as SearchIn From yourTable T
UNION
SELECT T.ID, 3 as FieldNo, T.Field3 as SearchIn From yourTable T

3. Create another query that identifies which records contain the
matches. Save this as qry_Matches. This query will give you the
record number, the keyword, and a count of the number of fields the
keyword was found in.

SELECT U.ID, WS.KeyWord, Count(*) as Matches
FROM qry_Union U, WordSearch WS
WHERE U.SearchIn LIKE '*' & WS.KeyWord & '*'
GROUP BY U.ID, WS.KeyWord

If you didn't have the union query, you could add a HAVING clause to
this query and identify which of the records contains all of the
keywords. However, since you have multiple fields that could match on
the same word, that won't work. You have to go one more step.

4. Create one last query that joins qry_Matches to WordSearch on the
KeyWord fields and returns a list of ID values that meet the matching
criteria. The sub-query will determine whether the record contains
all or just some of the key wordsand counts to determine whether the
number of words that are matched in a particular record equals the
number of words in the WordSearch table.

SELECT T.*
FROM yourTable T
WHERE T.ID
IN (
SELECT M.ID
FROM qry_Matches M
INNER JOIN WordSearch WS
ON M.KeyWord = WS.KeyWord
GROUP BY M.ID
HAVING Count(WS.KeyWord) = DCOUNT("KeyWord", "WordSearch"))


--
HTH

Dale Fye


Query to find records containing all words

I have a rather sloppy freeform Access 97 table that was imported from
an old DOS-based database. The information is haphazardly spread
among different fields in each record. Unfortunately, optimizing it
(cleaning it up) would be a insanely huge project. This means that
any searches must check all fields in each record.

Here's a simplified version of the current query where the criteria is
entered in a textbox on a form:

SELECT DISTINCTROW tblData.Field1, tblData.Field2, tblData.Field3,
tblData.Field4
FROM tblData
WHERE (((tblData.Field1) Like "*" & [Forms]![frmSearch]![txtCriteria]
& "*")) OR (((tblData.Field2) Like "*" &
[Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field3) Like
"*" & [Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field4)
Like "*" & [Forms]![frmSearch]![txtCriteria] & "*"))
ORDER BY tblData.Field1

This works fine if only one word is in the search criteria. But if
multiple words are entered, they are treated as a phrase.

I need to be able to query all of the fields in each record in this
table and return records that contain multiple criteria. In effect,
treating each record as one long field.

For example, if the user types "George" in a search field, the query
should return all records that have "George" in ANY field.

If the user types "George Washington", the query should return all
records that have"George" in any field AND "Washington" in any field.
Any record that did not contain both words (looking through all
fields) would not be returned.

If the user types "George Washington Carver", the query should return
all records that have"George" in any field AND "Washington" in any
field AND "Carver" in any field. Any record that did not contain all
three words (looking through all fields) would not be returned.

ETC, ETC...

I realize I'll probably have to limit the number of words that can be
used in a query in this manner, although it would be nice not to have
that restriction.

As part of the solution I presume I'll have to have multiple keyword
search fields or set up some kind of parsing on one search field.

Surely this isn't a unique problem. Thanks in advance for helping me
avoid reinventing the wheel.

--Don

 

[Don Hyams]

Thanks a lot for your ideas, Dale. I ended up using this method:

Instead of having only one search criteria textbox on the form, I
added two more (and named them txtCriteria1, txtCriteria2,
txtCriteria3). That seemed to have enough flexibility for the users.

My (simplified) query looks something like this:

SELECT DISTINCTROW tblData.Field1, tblData.Field2, tblData.Field3,
tblData.Field4
FROM tblData
WHERE ((([Field1] & " " & [Field2] & " " & [Field3] & " " &
[Field4]) LIKE "*" & [Forms]![frmSearch]![txtCriteria1] & "*" AND
([Field1] & " " & [Field2] & " " & [Field3] & " " & [Field4]) LIKE
"*" & [Forms]![frmSearch]![txtCriteria2] & "*" AND ([Field1] & " " &
[Field2] & " " & [Field3] & " " & [Field4]) LIKE "*" &
[Forms]![frmSearch]![txtCriteria3] & "*"))
ORDER BY tblData.Field1;

I concatenated all of the fields to compare with each of the search
criteria. If they only fill in one search field, the query returns
any records containing that one criteria. If they fill two fields,
the records returned must contain both criteria. Records must contain
all three search fields if they're filled.

NOTE: The four fields immediately after "SELECT DISTINCTROW" represent
fields that are used in a subform containing the search results. The
spaces added between the concatenated fields are to keep the fields
separated to avoid false returns caused by a string created by
adjacent field "overlap". In other words, if Field1 contained "Bob"
and Field2 contained "Cat", a search for "bobcat" wouldn't turn up
that record.

--Don

Don,

I've got done something similiar in the past and here is a quick
explaination of how I did it, this may not be the most efficient.

1. Create a WordSearch table that only contains on field (KeyWord,
text). When you hit the command button to execute your search, parse
your [Forms]![frmSearch]![txtCriteria] field using split and insert
each of the individual words into this table (don't forget to delete
all the previous records first).

2. Create a union query that collects all the text fields you are
interested in into a single virtual table. Save this query as
qry_Union.

SELECT T.ID, 1 as FieldNo, T.Field1 as SearchIn From yourTable T
UNION
SELECT T.ID, 2 as FieldNo, T.Field2 as SearchIn From yourTable T
UNION
SELECT T.ID, 3 as FieldNo, T.Field3 as SearchIn From yourTable T

3. Create another query that identifies which records contain the
matches. Save this as qry_Matches. This query will give you the
record number, the keyword, and a count of the number of fields the
keyword was found in.

SELECT U.ID, WS.KeyWord, Count(*) as Matches
FROM qry_Union U, WordSearch WS
WHERE U.SearchIn LIKE '*' & WS.KeyWord & '*'
GROUP BY U.ID, WS.KeyWord

If you didn't have the union query, you could add a HAVING clause to
this query and identify which of the records contains all of the
keywords. However, since you have multiple fields that could match on
the same word, that won't work. You have to go one more step.

4. Create one last query that joins qry_Matches to WordSearch on the
KeyWord fields and returns a list of ID values that meet the matching
criteria. The sub-query will determine whether the record contains
all or just some of the key wordsand counts to determine whether the
number of words that are matched in a particular record equals the
number of words in the WordSearch table.

SELECT T.*
FROM yourTable T
WHERE T.ID
IN (
SELECT M.ID
FROM qry_Matches M
INNER JOIN WordSearch WS
ON M.KeyWord = WS.KeyWord
GROUP BY M.ID
HAVING Count(WS.KeyWord) = DCOUNT("KeyWord", "WordSearch"))


--
HTH

Dale Fye


Query to find records containing all words

I have a rather sloppy freeform Access 97 table that was imported from
an old DOS-based database. The information is haphazardly spread
among different fields in each record. Unfortunately, optimizing it
(cleaning it up) would be a insanely huge project. This means that
any searches must check all fields in each record.

Here's a simplified version of the current query where the criteria is
entered in a textbox on a form:

SELECT DISTINCTROW tblData.Field1, tblData.Field2, tblData.Field3,
tblData.Field4
FROM tblData
WHERE (((tblData.Field1) Like "*" & [Forms]![frmSearch]![txtCriteria]
& "*")) OR (((tblData.Field2) Like "*" &
[Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field3) Like
"*" & [Forms]![frmSearch]![txtCriteria] & "*")) OR (((tblData.Field4)
Like "*" & [Forms]![frmSearch]![txtCriteria] & "*"))
ORDER BY tblData.Field1

This works fine if only one word is in the search criteria. But if
multiple words are entered, they are treated as a phrase.

I need to be able to query all of the fields in each record in this
table and return records that contain multiple criteria. In effect,
treating each record as one long field.

For example, if the user types "George" in a search field, the query
should return all records that have "George" in ANY field.

If the user types "George Washington", the query should return all
records that have"George" in any field AND "Washington" in any field.
Any record that did not contain both words (looking through all
fields) would not be returned.

If the user types "George Washington Carver", the query should return
all records that have"George" in any field AND "Washington" in any
field AND "Carver" in any field. Any record that did not contain all
three words (looking through all fields) would not be returned.

ETC, ETC...

I realize I'll probably have to limit the number of words that can be
used in a query in this manner, although it would be nice not to have
that restriction.

As part of the solution I presume I'll have to have multiple keyword
search fields or set up some kind of parsing on one search field.

Surely this isn't a unique problem. Thanks in advance for helping me
avoid reinventing the wheel.

--Don