Table structure help

[CHTHOMAS]

I have to develop an Access program for evaluating the results of a questionnaire. I need some help on the table structures. The questionnaire format is as below

Section A
1) HSE
1.1) Question 1
1.2) Question 2
1.3) Question 3
2) Availability
2.1) Question
3) Arrangements
3.1) Question 1
3.2) Question 2
3.3) Question 3

Section B
4) Management
4.1) Question 1
4.2) Question 2
4.3) Question 3
5) Motivation
5.1) Question 1
5.2) Question 2
5.3) Question 3

Depending on the responses of the questions, the evaluator will assign a score. Then there is a weightage and i have to do the calculations which i can.

My Problem
---------- is how to design the table? As a Single one containing all the sections or separate tables for each section?

In the report i have to show the result (ie Score x weightage) against each question(1.1, 1.2..), against each subsection(1, 2..), against each section(ie Section A, SectionB...).

Actually the questionnaire have upto 8 Sections each comprising many sub sections and subsub sections. I don't want to have a sloppy design and finding it difficult later on for querying.

Any ideas or suggestions will be highly appreciated.

 

[ghubbell]

Hi again Charley,
I have developed similar before, and although the queries after the fact are a little more difficult, your whole system will work well if you do choose to seperate each sub-section level in to their own tables. Just like the typical 'tree view' we are all used to seeing within Windows, by defining a level off of a level off of a level (and so on), you will be able to address and query each level on it's own, and provide greater ease of maintenance, plus your results will be more accurate. As below:

Sections Table
SectionID 1
Section & other fields

SectionsSub1 Table
SubSectionID1 1
SectionID (many)
SubSection1 info & other fields

SectionsSub2 Table
SubSectionID2 1
SubSectionID1 (many)
SubSection2 info & other fields

I hope you see where this is going.

;-)
Gord
gord@ghubbell.com

 

[Thanks]

Right to the point and i see it as the best solution.

Regards,

Charley