How do I keyword search using access like a library catalog?

[Guest]

I am creating a bibilographic database, and want to have a library catalog
feel. I want to be able to perform a query and search for items that have
certains words in there. Is there an easier way to do it than using the
wildcard figures? Also, can they be used in combination with the parameters
function. Ideally, I would like the user to type in a word or a couple of
words like a search engine and then query the database to spit out all items
that have those certain words in the "title" column.

 

[Dale Fye]

jkp

Here is a quick overview of the method I have used to do this in the past.

1. Create a new table (tbl_KeyWords), with a single field (KEYWORD)

2. When your user hits the Search button, parse the text box where they
have entered their key words, and write each of the key words to
tbl_KeyWords. You can make this procedure as complicated as you want. You
might want to simply use a space to parse the text, or you might want to
check for the existence of quotes, and identify everything between quotes as
a single key word. You might want to change the case of the Title and
KeyWord in the INSTR function below and just compare them in UCASE or LCASE,
or you might want to use theompare argument of the INSTR() function to
include a case sensitive search. You might even want to

3. Create a query (it might look something like below) to identify the
records that match your query string.

SELECT tbl_Titles.ID, tbl_Titles.Title,
Sum(IIf(InStr([Title],[keyWord])>0,1,0)) AS [Found]
FROM tbl_Titles, tbk_KeyWords
GROUP BY tbl_Titles.ID, tbl_Titles.Title
HAVING Sum(IIf(InStr([Title],[keyWord])>0,1,0)) > 0
ORDER BY Sum(IIf(InStr([Title],[keyWord])>0,1,0)) DESC;

You could make the query a little more complex and actually count the number
of occurances of each keyword within each title using the following code.

SELECT tbl_Titles.ID, tbl_Titles.Title,
Sum((Len([Title])-Len(Replace([Title],[Keyword],"")))/Len([KeyWord])) AS
[Found]
FROM tbl_Titles, tbk_KeyWords
GROUP BY tbl_Titles.ID, tbl_Titles.Title
HAVING
(((Sum((Len([Title])-Len(Replace([Title],[Keyword],"")))/Len([KeyWord])))>0))
ORDER BY
Sum((Len([Title])-Len(Replace([Title],[Keyword],"")))/Len([KeyWord])) DESC;

HTH
Dale