How do I show more "Or" tabs when applying filter?

[Guest]

I am categorizing our library's databases. Because each database may have
more than one possible category (out of 36), I have created a table where
there are 12 possible options of categories for each item (for example,
Academic Search Elite has 8 categories, while American National Biographies
has only 1 listed). I want to do filters wherein Access searches for all
possible items with a particular category (for example, "Biology") despite
whether it is the first or 8th category listed.

Unfortunately, I don't have enough "OR" tabs available to do a "Filter by
Form" search -- the scroll bar for my categories keeps getting in the way.

How do I get more "OR" tabs to show up -- or, failing that, is there a
better way to do the world's best "OR" search (i.e., Biology in Topic1 OR
Biology in Topic2... OR Biology in Topic 12)?

Thanks for your help!

 

[Dirk Goldgar]

I am categorizing our library's databases. Because each database may
have more than one possible category (out of 36), I have created a
table where there are 12 possible options of categories for each item
(for example, Academic Search Elite has 8 categories, while American
National Biographies has only 1 listed). I want to do filters wherein
Access searches for all possible items with a particular category
(for example, "Biology") despite whether it is the first or 8th
category listed.

Unfortunately, I don't have enough "OR" tabs available to do a
"Filter by Form" search -- the scroll bar for my categories keeps
getting in the way.

How do I get more "OR" tabs to show up -- or, failing that, is there a
better way to do the world's best "OR" search (i.e., Biology in
Topic1 OR Biology in Topic2... OR Biology in Topic 12)?

Thanks for your help!

A table that has fields Topic1, Topic2, Topic3 ... Topic12 is badly
designed for querying. You would do better to have a "narrow" table
allowing for multiple records per item, along these lines:

ItemTopics
------------
ItemID (foreign key to a table of Items)
Topic (combines with ItemID to make primary key)
TopicOrder (maybe -- a number to order the topics by relevance)

Then you wouldn't use Filter by Form to search, but you could use your
own search form to enter criteria such as topic, and then run a query to
find matching records. For example, a query to find all items on the
topic "Biology" would be something like this:

SELECT DISTINCT Items.*
FROM Items INNER JOIN ItemTopics
ON Items.ItemID = ItemTopics.ItemID
WHERE ItemTopics.Topic = "Biology";

Notice that now you don't have to enter any such long WHERE clause like

Topic1 = "Biology" OR
Topic2 = "Biology" OR
Topic3 = "Biology" OR
Topic4 = "Biology" OR
Topic5 = "Biology" OR
Topic6 = "Biology" OR
Topic7 = "Biology" OR
Topic8 = "Biology" OR
Topic9 = "Biology" OR
Topic10 = "Biology" OR
Topic11 = "Biology" OR
Topic12 = "Biology"