table design

[Jeff C]

I have a sets of questions I want to answer on different pieces of equipment
and I am not able to figure out how to relate the tables to one another. I
have tried several different approaches. Having built one-to-many
relationships between the equipment, sets of questions, and answer options
for the questions several different ways now, my problem seems to be defining
the query/table that will hold the separate answers to all the questions for
each piece of equipment.

Table 1 = models with mdl_ID Pkey – equipment w/descriptors including the g_ID
Table 2 = Groups with g_ID as Pkey – defines groups of questions
Table 3 = questions with q_ID as Pkey – includes g_ID
Table 4 – answer options for each question

I have gone back and started over 3 times now so once more will not hurt. I
have basically used an autoform beginning with the answer table which I then
use as a subform after using an autoform on the question table/query which I
then insert as a subform on a form made from the mdl table. I select the
answers on the first device and I am not able to answer the questions for the
rest of the equipment. I have looked at Mr. Hookums survey example but
cannot relate mine to his in a way I can figure out. I’d appreciate any
ideas..thanks.

 

[Sharkbyte]

Jeff:

Are your answers text based or multiple choice?

Sharkbyte

 

[Jeff C]

The answers are mixed, several have a free text answer, others are text
answers and multiple choice. I have them in a table with a yes/no box as a
field so that a check in the yes/no box indicates the answer. Thanks

 

[Sharkbyte]

Jeff:

As far as tables go, you are pretty much there. However, for Table4 I would
add:

tblTable4
AnswerID
QuestionID (PK) (FK)
ModelID (PK) (FK)
Answer

I'm not really understanding how you are using a subform to put the answers
onto a questions subform. I would suggest a query, based on the mdl_ID, that
has questions and answers, together. Then make 1 subform from that, linked
to your mdl_ID on the main form. Then as you cycle through your Models, the
subform will update to match.

HTH

Sharkbyte