Single choice from multiple fields

[YSong]

Hello experts, I need your help as I am frustrted with one query of my
database of collection of articles. Each article can have up to 5 keywords.
So I have made two tables. One is the KeywordList table which contains
KeywordID (primary key) and KeywordText to list all the keywords. The other
is ArticleDetail table, which contains 5 combo boxes called keyword1,
keyword2 and so on to keyword5 to look up values of keyword from KeywordList
table. It works well when I choose the keyword from the drop-down list box to
fill in the records.

The problem happens when I tried to make a query by keyword. After joining
5 KeywordList tables with the ArticleDetail table through KeywordID, I set
the criteria [input keyword] under each field name of Keyword1, Keyword2,
etc. in different row as "OR" operation, trying to retrieve all the articles
with one SPECIFIC Keyword. But The query cannot return ALL the records that
contain that specific keyword in either keyword field of the 5 fields. Only
part of the records are returned and some of those even DON'T contain the
SPECIFIC keyword input.

I have spent several days on the query and couldn't figure it out. Is there
anything wrong with my setup? Any of your help will be highly appreciated. If
you would like I could send my database through email for you for a
diagnosis. Thanks a lot in advance!

 

[Allen Browne]

Since one article can have many keywords, you need to use another related
table for the keywords to the article.

Assuming your Article table has an AutoNumber primary key named ArticleID,
the new table will have these fields:
ID AutoNumber
ArticleID Number relates to Article.ArticleID. Required field.
Keyword Text a keyword for this article.

Now create a subform on your Article.
The subform will be in Continuous Form view.
You can add as many (or as few) keywords as you want for any article - one
per row in the subform.

Now you have only one field to search to find the articles that match a
keyword.

 

[YSong]

Thank you very much for your reply and big help. I'll try as you suggested. I
may come back to tell you the result or more questions. Thanks!

Since one article can have many keywords, you need to use another related
table for the keywords to the article.

Assuming your Article table has an AutoNumber primary key named ArticleID,
the new table will have these fields:
ID AutoNumber
ArticleID Number relates to Article.ArticleID. Required field.
Keyword Text a keyword for this article.

Now create a subform on your Article.
The subform will be in Continuous Form view.
You can add as many (or as few) keywords as you want for any article - one
per row in the subform.

Now you have only one field to search to find the articles that match a
keyword.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello experts, I need your help as I am frustrted with one query of my
database of collection of articles. Each article can have up to 5
keywords.
So I have made two tables. One is the KeywordList table which contains
KeywordID (primary key) and KeywordText to list all the keywords. The
other
is ArticleDetail table, which contains 5 combo boxes called keyword1,
keyword2 and so on to keyword5 to look up values of keyword from
KeywordList
table. It works well when I choose the keyword from the drop-down list box
to
fill in the records.

The problem happens when I tried to make a query by keyword. After
joining
5 KeywordList tables with the ArticleDetail table through KeywordID, I set
the criteria [input keyword] under each field name of Keyword1, Keyword2,
etc. in different row as "OR" operation, trying to retrieve all the
articles
with one SPECIFIC Keyword. But The query cannot return ALL the records
that
contain that specific keyword in either keyword field of the 5 fields.
Only
part of the records are returned and some of those even DON'T contain the
SPECIFIC keyword input.

I have spent several days on the query and couldn't figure it out. Is
there
anything wrong with my setup? Any of your help will be highly appreciated.
If
you would like I could send my database through email for you for a
diagnosis. Thanks a lot in advance!