Query of three fields simultaneously

[acores]

Hello. Can anyone help with this problem, please?

I am building a library database and I created the table «tbl_keywords».
This table has only one field named «Keywords» and I inserted some records on
this table such as «Fictional», «Romance» and «Technical», for example.

I inserted three combo boxes in the book form «frm_book» to allow the user
to choose the keywords associated to each book. The user can choose up to
three keywords of the table «tbl_keywords» for each book (one on each combo
box). Each keyword chosen is saved on the books record, as «kw1», «kw2» and
«kw3».

Everything is working fine, until this: I want to create a query that allows
the user to see all the books of one certain type, I mean, keyword. Imagine
that you need to see all the «Romance» books, for example. The problem is
that the query must search the keyword «Romance» in the three fields «kw1»,
«kw2» and «kw3» of the table «tbl_books», because the user could have chosen
«Romance» in one of the three combo boxes.

I hope I made myself clear, but ask me if you have any question, please.

Thanks in advance.

Best regards,

Acores

 

[Arvin Meyer [MVP]]

Since a book cannot be more than one type, you only need 1 combo to search
that type. Storing multiple values in a single field (regardless of whether
or not it is possible to do so) is poor database design and a violation of
database normalization rules.

 

[acores]

Thanks for your answer, Arvin.

But the problem is that a book can be more than one type. The example I gave
is not so happy but I tried to ilustrate the situation. Imagine for example
that you want to associate to a certain book the keywords «War», «Drama» and
«Terror». That is why I have three combo boxes in order to allow the user to
choose more than one keyword for each book. Do you understand what I mean?
(my English is not so good)

Thank you in advance for your help.

Best regards.

Acores

"Arvin Meyer [MVP]" escreveu:

 

[Douglas J. Steele]

The problem would appear to be that you've actually got a many-to-many
relationship between books and keywords, which means that you should be
storing each keyword as a separate row in a third table to resolve the
many-to-many.

Take a look in the Northwind database that comes with Access. The
relationship between Products and Orders is also a many-to-many (one Product
can appear on many Orders, one Order can contain many Products), so table
Order Details was created.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)