Question about questionnaires (1 Viewer)

[Anakardian]

Hi everybody. After giving up on my last attempt at learning access, I have decided to come back for more. I have decided to try to make a database that will help us analyse the various inspections that are carried by outside companies. I got most of the basic tables needed for this figured out however I have hit a snag. Since I would like to be able to get several possible groupings from the questionnaires being used, I think I need to have a table that contains this information. The problem: The questionnaires contain chapters, subchapters and questions. There is no immediate identifier to the subchapters except a title that may be repeated in various other chapters or even in the same chapter. My possible solution: I could make a table that contains the chapter number and title, the subchapter title and question numbers and then adding an extra entry to group the subchapters. While this could probably work, I do not think it is very robust. The question: How would you go about solving such a thing on a table level?

 

[Anakardian]

Not sure why line breaks refuse to work so apologise for the messy text

 

[PaulWilson]

Sounds like a classic "drill down" design.
One chapter contains many subchapters. One Subchapter contains many questions.
Is that right?

 

[Anakardian]

More or less. I initially thought the title of the subchapter could serve to differentiate the subchapters but since the same title can occur more than once in the same chapter that idea is out. The problem occurs when we receive the observations. They are just listed by the number and it would folly to have to first select a chapter, unknown subchapter and then the question number. I thought of making a table to link each question to a subchapter but it seems cumbersome to do that. The idea of including the subchapter is that we want to run some statistics on them as well.

 

[PaulWilson]

My sympathies.
I do a survey database. One survey is linked to many questions and each question is linked to many answer choices. One question can be on multiple surveys and one answer choice can serve for many questions. Accordingly, I have a junction table between the survey table and the question table. The junction table has its own surrogate key, a survey key, a question key and an answer choice key. The same junction table links the survey table to the answer choice table.

The design works but it requires custom record navigation on forms because the junction table contains many records depending on the number of answers per question.

 

[Anakardian]

Since I am at the level of an apprentices apprentice that sounds quite complicated.

Would you mind enlightening my a bit on how you cracked such a nut?

 

[PaulWilson]

That's why I say I sympathize.
The idea of a survey seems easy, but when you get into it you'll find it difficult.
I attach a screenshot of the relationships window. I wish I could do more now but we're preparing for a hurricane here in my part of NC!

 

[Anakardian]

From the relationships it also seems complicated....
In some way it seems similar to what I am trying to do so I will see if there is something I can learn from thinking on it.

 

[Anakardian]

I hope Irene will not be too bad when she hits you

After thinking on how you solved your problem, I think I have a way of solving mine.

By arranging the tables as set out below, do you think it would work?

Table for inspection type:
Autonumber (PK)
Inspection type (Could this serve as the primary key?)

Table for chapters:
Autonumber (PK)
Inspection type (FK)
Chapter number
Chapter title

Table for subchapters:
Autonumber (PK)
Inspection type (FK)
Chapter number (FK)
Sub-chapter title

Table for questions:
Autonumber (PK)
Inspection type (FK)
Chapter (FK)
Sub-chapter (FK)
Question number

I think this will allow a way of linking each question to its sub-chapter and main chapter.
Of course the elegant solution would be to extract this info from the way the question numbers are constructed but sadly this does not appear to be possible with the way the questionnaires are arranged.