Table design for questionnaire data

[groovybunny]

Hi There (Apologies if this doesn't make sense!)

I need to store results from an paper audit so that the data can be entered via a form and interrogated through queries.
I have 6 text fields but the remaining 30 questions are multiple choice.
I also need to store date, time, who the auditor was

I originally created one table but this was a mistake, it was huge and I cant interrogate the data!

I want the user to select the answers from relevant drop downs.

I have created different question types based on answer types ie
Type 1. Y/N ....Type 2. a,b,c,d,e.... Type 3. 1,2,3,4,5 etc.
I have 7 different question types.

Is the correct way to do this via a few tables?

Do I need one question table for each question type, with 2 columns, an ID then Type? (so 7 tables)
And 7 corresponding answer tables ID then Type?
I have a client table already so will i need to store the ClientID in the answer table?
I also need to store date, time, who the auditor was. would this need to be in a separate table?

Apologies if this is blindingly obvious but I have been looking at it for so long it has morphed in to gibberish!

Any help gratefully received

 

[CT Witter]

Here is one way

ACC2000: Sample Table Design to Support Questionnaire Applications
http://support.microsoft.com/kb/209493

HTH,
CT

 

[groovybunny]

Thankyou, thats exactly what I was looking for!