Assigning groups to individual items

J

Jeff C

I have a large database of equipment which is divided into 4 catagories based
upon a yes/no field for each category. I could just as easily assign a
number 1-4 in a catagory field, whatever is best. I built a two field table
containing questions in one field, which have to answered on the equipment
and the questions are catagorized based on a numbering system, 4 groups of
questions.

Can someone help me with an idea of how to set up the tables so I can relate
a set of questions to each piece of equipment based upon the category of the
equipment? I don’t know if I should split my table of questions into
individual tables..one for each category or not. I am stuck on this one.
Thanks for any help.
 
N

Nikos Yannacopoulos

Jeff,

The four yes/no fields are not a good idea. Suppose at some point you
have to introduce a fifth category; you will need to add a new field to
the table, and then go change the design in all queries/reports it is
used:-(

Categories -> Equipment is a classic one-to-many relationship, therefore
the way to model it is by use of a separate table for categories, and a
join on the ID field with the equipment table.

My advice is change the table design immediately. Take the following steps:

1. Add a table, say, tblCategories, with two fields:
CatID (Integer or Autonumber)
Category (Text)

2. Add a CatID field in your equipment table, and use an update query to
populate it based on the existing four yes/no fields. You could do it in
four steps, or just one using an update expression like:
-[Cat1]*1 - [Cat2]*2 - [Cat3]*3 - [Cat4]*4
where Cat1 thru Cat4 are the yes/no fields - substitute your actual
names. The expression makes use of the fact that yes/no fields return 0
for No, -1 for Yes in a mathematical expression.

3. Add a relationship between the nrew table and the equipment table on
the CatID field, and enforce referential integrity (cascade update > Yes).

4. Remove the obsolete yes/no fields from the equipment table.

5. Make sure you have a CatID (or equivalent compatible) field in the
questions table; you can now join the questions table to the equipment
table in a query on the field. Adding a relationship between
tblCategories and tblQuestions (or whateber it's called) on filed CatID,
and enforcing referencial integrity, would also be a good idea.

HTH,
Nikos
 
Top