fields with multiple answers 3

[carlosguill]

Hello, I am returning to this site after I found a great deal of help with previous questions, and I hope I can get some help with this question also.
I am setting up an Access table about social/medical data that has approximately 50 fields, each field has 6 answers that all can be checked at once, or (here comes one of the problems) some can be done at different times, but every time an answer for any of these fields gets filled in I need to keep track of the dates. Also not all the fields' info are obtain in the first time, some fields can have partial info at the beginning or none. I'll give an example to try to make some sense of this puzzle.


question answer1 answer2 answer3 answer4 answer5
depression yes yes yes yes yes

I'll appreciate any help with this problem if this makes any sense to any body.

C. Armas

 

[SQLSister]

My first comment is that I would use two tables not one.
QuestionTable
Question
QuestionID

Answer table
QuestionID
Answer
AnswerDate
This way you are not limited to 6 answers per question. If it has been only answered once, there will only be one record, if it has been answered 6 times, there will be 6 records.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[johnherman]

SQL Sister has the right idea. When I worked in Health Services, we often had Surveys and Questionnaires with answers such as yours. I don't have our survey layout handy, but I think you would be wise to consider implementing a survey kind of solution for this problem.


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[willir]

Hey, SQLSister
Very well done on getting the TipMater award this week. I always knew you were hot, but you smoked last week!!

carlosguill
I posted a couple item on the following thread. Although I only got one star for my efforts, what really made my day was when a colleague stated that his team came to a similar conclusion on the design issue.

http://www.tek-tips.com/viewthread.cfm?qid=844594

Basically, you have 40 odd questions in your database. But what do you do if you need to add more? What if you want to re-use the design? The approach you listed is more of a "flat file" or "spread sheet" approach that may limit its functionality later on. In the posted example, the issue Pyrrhus had was that his survey exceeded beyond the 255 field limit on a table.

Richard

 

[dhookom]

There is a full working demo of a normalized survey application with everything needed for simple surveys at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

As usual, good stuff Duane.

 

[carlosguill]

Thank you everyone for your suggestions, I am going to start trying to digest what you advise and if I get stack I'll get back to you soon. Thanks a lot.
Carlos

 

[SQLSister]

Thank you Richard, I didn't even realize I had gotten so many stars last week. So it came as a total surprise.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[carlosguill]

I am finally able to find time to work on this project, but I have a question about the set up of the tables that SQLSister proposed. For the Answer Table, do I need to use a look up table to include the choice of response for the field Answer?

 

[lotharious]

From SQLSisters response it seems as if she left out 1 table for the purpose. So I shall try to expand upon it.

Question Table - The question being proposed
Question
QuestionID
'Depressed', '1'

Answer Table - List of Answers to the Question
QuestionID
Answer
'1', 'Acute'
'2', 'Mild'
'3', 'None'

*missing table*?
Data Table - Holds the answers given by the surveyed.
QuestionID
Answer
'1','3'

Hope this helps some.

 

[carlosguill]

Thank you, I think this is what I needed.

 

[carlosguill]

One more question, do I need to keep the field QuestionID in the Answer Table?

 

[PHV]

do I need to keep the field QuestionID in the Answer Table
You have to know which question the answer is for, don't you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carlosguill]

Refering to the second table "Answer table" wouldn't I Need a field AnswerID and not Question ID?

 

[PHV]

I guess you need both with PrimaryKey on this 2 fields, QuestionID as ForeignKey referencing the Question table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carlosguill]

To make sure I am in the right path, is this what I would end up with?

Question Table
Question
QuestionID
'Depressed', '1'

Answer Table - List of Answers to the Question
AnswerID
Answer
'1', 'Acute'
'2', 'Mild'
'3', 'None'

*Third table*?
Data Table - Holds the answers given by the surveyed.
QuestionID
Answer
'1','3'

 

[PHV]

Answer Table - List of Answers to the Question
How do you know the question the answers refer to ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carlosguill]

Sorry, if I am a little confused, but Wouldn't table three store the question ID and the answer to that specific question?

 

[PHV]

A question may have many answers ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carlosguill]

There are about 50 questions and 5 possible answers, all the 50 questions are answered during the same interview, but each question gets only one answer, although the same question will be asked again at a later time.