Twist on Evaluation / Survey Data Collection

[elbano]

I am working to set up a database to evaluate machines in our plant. Each department manager will evaluated the machines in their respective departments with a series of 10 multiple choice questions (each answer/response has a specific point value).

Here's the twist. I would like the manager to evaluate all of his machines, one question at a time:

Question#1:
What is the....?
Machine1: Response to Q1
Machine2: Response to Q1
.
.
.

Question#2:
How much...?
Machine1: Response to Q2
Machine2: Response to Q2
.
.
.

I currently have the following tables:

tblQuestions
QuestionID-PK (AutoNumber)
Question#
Question (text)

tblAnswers
QuestionID-FK
AnswerID-PK (AutoNumber)
AnswerPosition (I.e. 1.1,3.2 -> 1st answer to Q1, 2nd answer to Q3, respectively)
Answer (text)
Points (point value for each answer)

tblMachines
MachineID-PK (AutoNumber)
Machine (test)
Department-FK

I'm not sure how I should set up the table that links the responses to the machines.
I'm not sure how I need to set up the table that links the answers (responses) to the machines. Would every answer/response require a new record? Should every question/answer set have its own table?

If it were a spreadsheet, I would set it up as so:
Col1=Machines Col2=Responses to Q1 Col2=Responses to Q2...

Tab1=Machines in DeptA Tab2=Machines in DeptB...

I don't think this would be the proper way to set up the table. Would someone please help steer me in the right direction?

I looked at quite a few tutorials and examples, Unfortunately, the simple examples do not meet my needs, and the more flexible examples are over my head (brackets vs dots vs bangs, collections, "Me./Me!", "cmd.", etc.) I'm self taught and have been going around in circles for weeks. Please help steer me in the right direction.

*Note - I would like to follow the following rules (Unless someone can convince me that they are not correct):
- Avoid the use of the lookup wizard in my tables
- Use AutoNumber for primary key wherever possible
- Use drop down boxes in my form/subform to populate data (user sees relevant text, but tables are populated with numbers/FKs)

 

[PHV]

You need a junction table for a many-to-many relationship:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You might want to review At Your Survey which has a junction table that links responses/answers to a "header" record.

Duane
Hook'D on Access
MS Access MVP