Configure a "keyword" option for a library database

[SPRUSO]

Designing an MS Access 2003 VER database for the recording and recall of
library records.
Would like to have a 'keyword search" option to recall library records
titles and/or authors.Can Access be configured to do this?, if not can I link
orintegrate MS Windows Desktop Search into Access and use it as the keyword
option?

 

[TC]

I would do something like this. Say you have the following table for
the library articles.

tblArticle
ArticleID primary key
Title
Body (memo field)

Create a second table:

tblKeyword
ArticleID } composite
word } primary key

Then, in every form (or other place) that can create, edit or delete
tblArticle records, write code that scans the Title, and the Body;
extracts the list of unique words from both; and creates records in
tblKeyword for each of those words.

For example, if the Title and Body of ArticleID #999 contained only
three unique words: "tom", "dick", "harry", you'd creat the following
three records in tblKeyword:

999 tom
999 dick
999 harry

And of course, that table would contain the keywords from all the other
articles:

888 now
888 then
888 tom
888 jane
etc.

So if you searched tblKeyword for "tom", for example, you would
instantly see that "tom" is present in articles #999 and #888 - without
actually having to search those articles at that point.

The main trick here would be to ensure that the records in tblKeyword
were kept accurate, as the user not only added new articles, but also,
edited or deleted existing ones.

HTH,
TC [MVP Access]

 

[Fred Boer]

Dear SPRUSO:

Another option, in addition to TC's suggestion, might be to take the keyword
value, and use it in a query which searches each revelvant field in turn.

You might end up with a SQL statement like this:

SELECT Qry_AllLibrary.*
FROM Qry_AllLibrary
WHERE (((Qry_AllLibrary.Author) Like "* dog*")) OR (((Qry_AllLibrary.Title)
Like "* dog*")) OR (((Qry_AllLibrary.Subject) Like "* dog*")) OR
(((Qry_AllLibrary.Series) Like "* dog*"));


HTH
Fred Boer