I have a table organized two different ways. One with order numbers on the (key) and the category 1 -13 as the field and then another table with names of categories on the side and order numbers at the field. How would I search for any order that has a certain category or any category that is associated with a certain order? I find that with the first table (order number as key and categories as the field)) that I cannot put a category name in each column for the criteria. I cannot search in just one field for the category because on one record it could be in field one and in aother it could be in field two.
You're missing the point of how tables work! This appears to be good
spreadsheet logic - but good spreadsheet logic is often bad relational
database logic.
Storing data - categories - in fieldnames IS BAD DESIGN and will make
your query all but impossible. Storing the same data in two tables is
even worse.
If you have a many (orders) to many (categories) relationship, the
proper design is to have *three tables*:
Orders
OrderID <primary key>
<information about the order as a whole, no category info>
Categories
CategoryNo <Integer, primary key>
CategoryName
OrderCategories
OrderID <link to Orders>
CategoryNo <link to Categories>
So if an order fell under seven categories, you'ld have seven records
in the OrderCategories table.
