Using Keywords to search

[Guest]

I'm new to using Access, and databases in general. I'm trying to set up a database of reference articles. My table has the following fields: Authors, publication date, title, journal name, volume, page #s, publisher, and Full article (attached as OLE Object). I want to have another field for keywords, where I can list the keywords that are associated with the article. I want people who use the database to be able to search for articles by their keywords. What I'm running into is, if there's more than one word in the cell, a query won't pull up articles with more than one keyword. For example - One article has only "lifting" as a keyword, and another article has both "lifting" and "compression". If I run a query using the word "lifting" to pull up articles, the query won't pull up the article with both "lifting" and "compression".

How can I format this table so that I can search for articles using multiple keywords in one cell

Any help would be appreciate

Nadia

 

[Evan]

Nadia

You can search for one keyword in your tables' field and return all records
that match in the table irrespective of whether they have multiple keywords
or not

This is done using the the "Like" operand. So if you are looking for
"lifting" your search criteria would be Like "*lifting*". The two * before
and after mean you don't care what keywords before lifting and which come
after.

However searching for two keywords is a slightly different ball game. If
your users want to search for articles containing keywords "lifting" and
"pushing" then the criteria will have to be - Like "*lifting*" and Like
"*pushing*.

Not tried this but I'll bet it can get messy!

Evan

I'm new to using Access, and databases in general. I'm trying to set up a

database of reference articles. My table has the following fields: Authors,
publication date, title, journal name, volume, page #s, publisher, and Full
article (attached as OLE Object). I want to have another field for
keywords, where I can list the keywords that are associated with the
article. I want people who use the database to be able to search for
articles by their keywords. What I'm running into is, if there's more than
one word in the cell, a query won't pull up articles with more than one
keyword. For example - One article has only "lifting" as a keyword, and
another article has both "lifting" and "compression". If I run a query
using the word "lifting" to pull up articles, the query won't pull up the
article with both "lifting" and "compression".

 

[Pavel Romashkin]

You can use a "LIKE" query to search for partial matches:

SELECT * FROM tblArticles WHERE tblArticles.Keyword LIKE "* lifting*"

However, I think it would be more appropriate to set up a separate table
for keywords, and then create a junction table:

tblArticles
ArticleID
Author etc.

tblKeywords
KeywordID
KeywordDesc etc.

tblKeywordLinks
ArticleID
KeywordID

This way you can recycle your keywords (use the same keyword for
different articles that have it in common). Keyword searches will be
more efficient than LIKE queries on the denormalized Keyword field in
the Articles table if your database will grow large.
Hope this helps,
Pavel