Append a column. Transpose a row.

[ROC]

I have an Access 2007 project that requires the user to respond to a
group of questions. The results of those responses are then stored
with other responses and then compared with one another. The
questions table includes a list of column qualifiers and both the
questions and qualifiers change over time. I want to store the
responses in a different table by adding the participants response as
a new column using an autoid field as the link. Makes sense? What is
the best way to approach this problem? I am not a programmer. I just
use the mechanical controls of Access.

Table

Id Question code1 code2 code3 code4
1 How many widgets n y y
y
2 How big box x
3 Size of circle x y

New Table
Qualifier = code3 Participant Id
Id Question Mary John
1 How many widgets x x
2 How big box
3 size of circle x x

 

[John W. Vinson]

I have an Access 2007 project that requires the user to respond to a
group of questions. The results of those responses are then stored
with other responses and then compared with one another. The
questions table includes a list of column qualifiers and both the
questions and qualifiers change over time. I want to store the
responses in a different table by adding the participants response as
a new column using an autoid field as the link. Makes sense? What is
the best way to approach this problem? I am not a programmer. I just
use the mechanical controls of Access.

Table

Id Question code1 code2 code3 code4
1 How many widgets n y y
y
2 How big box x
3 Size of circle x y

New Table
Qualifier = code3 Participant Id
Id Question Mary John
1 How many widgets x x
2 How big box
3 size of circle x x

Neither approach is correct. You need THREE tables:

Questions
QuestionNo
Question <text>

Participants
PersonID
LastName
FirstName
<other info as needed>

Answers
PersonID <who's answering>
QuestionNo <what they're answering>
Answer

"Fields are expensive, records are cheap" - you need a tall thin table, not a
proliferation of new table fields.

 

[ROC]

John,

Thank you. I mean REALLY thank you. Your quote is very appropriate.
I lost sight of a fundamental rule. Everything works but for this
problem. I'll ask it here and post it if that is the better choice.
I have linked the Criteria and Questions fields. I need to add
questions to the linked criteria field while the criteria field
remains the same. For example, the first criteria might be "lighting"
and the first corresponding question would be "Are the lights bright
enough?" The second question as yet unfilled would include lighting
as the criteria and then a blank space for the yet to be filled
question. How can I autofill the criteria prior to entering the
question - I presume by an append query or editing in a form?

Bob C.