Expert: Scottgem - 2/14/2005

Question
I'm sure this is probably a very easy question but I am new to access and having a problem setting this up correctly. I am creating a book inventory program.  I have a table with some fields that lists different topics the book might fall under.  The datatype for these fields is Yes/No.  I would like to set up a parameter query QrySearchbyTopic linked to a form FrmSearchtopic that asks the user to check what topics they want to search for.  So a user might want to search for books falling into any of the 5 topics.  They can also choose more than 1 topic.

I have tried using the following criteria:

First Field:
Field:     Topic1
Table:     TblBooks
Criteria:  [Form]![FrmSearchTopic]![Topic1]=-1

Next 4 Fields:
Field:     TopicX (where x is 2 through 5)
Table:     TblBooks
Criteria:  
Or:        [Form]![FrmSearchTopic]![TopicX]=-1

I also tried setting the Criteria for Topic1 to: Like[Form]![FrmSearchTopic]![Topic1] and then adding a new Field [Form]![FrmSearchTopic]![Topic1]
with Criteria: -1

Neither method gives me the correct records.  Any help that you could offer would be really appreciated.

Thanks.

Michelle  

Answer
The problem here is that your database is not properly normalized which is causing your querying problems. I would strongly suggest that you do a WEB search on Database Normalization. You should find several references to help you understand how to do this. A properly normalized database will be easy to query and maintain.

The main problem is your checkboxes represent what's called a repeating group. Such a structure will always cause problems with querying.

What you need is three tables. First, is tblBooks which has your info about each book (or title). Second is tblTopics which is a lookup table listing all the topics you want to use. The structure would be:
TopicID (Primary key - autonumber)
Topic

The third is a "join" table (call it tblBookTopics). The structure would be:
BookTopicID (PK Autonumber)
BookID
TopicID

This structure has several advantages. For one, with your structure, adding a topic would mean redesigning tables, forms and queries, Maybe reports. With a properly normalized structure you just need a record to tblTopics.

When doing data entry, you would have a subform in which the user can add topics for the book. The subform would be set up as continuous form mode so the user can see the list of topics.

To query this structure means setting up a multi-select listbox that queries tblTopics. The selection is then used to run a query. You will find an example of how to do this here:
http://www.mvps.org/access/forms/frm0007.htm

HTH
Scott<>