Hi,

I have the task of creating a new questionaire and associated reporting. Normally I would have done this in Excel, but due to the size I feel it is neccessary to create this in Access.

My question is should I revise my table of completed forms? I initially planned to create a form to populate the completed forms table. Job done?
However I am sure I have read somewhere this is not the best in terms of reporting and I should have a table of answers for each question? Is this correct I have approx 50 questions?

I have the following tables:

1) Staff Details
2) Table Of Questions
3) Table Of Answers (Questions are multiple choice)
4) Table of completed forms

The recommendation is not a separate TABLE for each question but a separate ROW. When you do this with a spreadsheet, your first inclination is to create a separate COLUMN for each question. So you would have one row per response with 50 columns. In a relational database, the proper structure is two tables. One that houses the things that occur once per survey such as who took it, when it was taken, etc. The second table holds 50 ROWS, one for each answer.

Search here for survey and questionnaire for suggested structures.

If you go with the 50 columns approach, you'll be back in a few days when you figure out the amount of coding and number of queries that will be required to do even the simplest of analysis.

I'm not sure Pip was considering a seperate table for each question...???

Thanks guys,

Unfortunately I was considering 50 tables!

I had researched and found the 50 columns was wrong, but when I got back to working on it I had been unable to find the same reference! I for some reason thought it was 50 tables and 50 rows, didn't occur to me!

I think I am still stuck in my Excel head as I'm still spend 80% of my time working in it! The 50 rows makes a lot more sense.

Thanks again, I should be OK from here (hopefully)!

Cheers

Phill