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

 

[PHV]

So, 2 tables suffice:
Question Table
Question
QuestionID
AnswerID
'Depressed', '1', '3'

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


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

 

[willir]

Well, I assume you are going to "ask" the questions to more than one person or site, etc. Otherwise, you are going about a lot of work to ask a set of questions once, or you plan to over-write the question / answers each subsequent time.

So I would suggest you consider adding a table to track whom is being asked, either a site or contact or patient, etc. I will use Patient for an example.

tblPatient
PatientID - primary key
PatientLN - last name
PatientFN - first name
... etc

The contact, patient or whatever needs to be tied to the answer or response.

Next, I can't believe that you will retype the questions over and over again.

From my perspective, SQLSister was on the right track. carlosguill follow up was on track too. Perhaps some "tweaking" of the tables names might make idea more obvious...

tblQuestion
QuestionID - primary key
Question - text

tblPossibleAnswers
PossibleAnswerID - primary key
QuestionID - foreign key to tblQuestion
PossibleResponse - text

If the responder can only choose one answer...

tblAnswers
PatientID - foreign key to tblContact
QuestionID - foreign key to tblQuestion
PossibleAnswerID - foreign key to tblPossibleAnswers
DateAnswered - Date
Comments - memo

primary key = ContactID + QuestionID

If the responder can only choose one or more answer...

tblAnswers
AnswerID - primary key
PatientID - foreign key to tblContact
QuestionID - foreign key to tblQuestion
PossibleAnswerID - foreign key to tblPossibleAnswers
DateAnswered - Date
Comments - memo

Putting it together...
[tt]
tblPatient
PatientID PatientLN PatientFN
1 Smith John
2 Lee Cindy

tblQuestion
QuestionID Question
1 Rate your level of happiness right now?
2 Do you get headaches writing programs?

tblPossibleAnswers
PossibleAnswerID QuestionID PossibleResponse
1 1 Very happy
2 1 Moderately happy
3 1 Slightly unhappy
4 1 Unhappy
5 1 Very unhappy
6 2 Always
7 2 Sometimes
8 2 Rarely
9 2 Never
[/tt]

Assuming only one answer per question...

[tt]
tblAnswers
PatientID QuestionID PossibleAnswerID DateAnswered
1 1 2 Jan 30, 2005
1 2 8 Jan 31, 2005
2 1 1 Jan 15, 2005
2 2 7 Jan 28, 2005
[/tt]

So...
- John is moderately happy, and rarely gets headaches;
- Cindy is very happy, and sometimes gets headaches

Now, assume that a person can supply more than one answer, or in this case, can respond to the question on different dates...

[tt]
tblAnswers
AnswerID PatientID QuestionID PossibleAnswerID DateAnswered
1 1 1 2 Jan 05, 2005
2 1 2 8 Jan 06, 2005
3 2 1 1 Jan 15, 2005
4 2 2 7 Jan 28, 2005
5 1 1 3 Jan 25, 2005
6 1 2 8 Jan 26, 2005
7 2 1 2 Jan 30, 2005
8 2 2 7 Jan 31, 2005
[/tt]

So here John was moderately happy on Jan 5, and slightly unhappy on Jan 25, and Cindy was very happy on Jan 15, and moderately happy on Jan 30.

By plugging data into your model, and then running test queries, you can see if you are going to be able to get the information you require.

Richard

 

[carlosguill]

Thank you Richard, I think this captures exactly what I need, although I still have a question about the QuestionID in the tblPossibleAnswers, I have a total of 55 questions, but the set of answers is always the same for all the questions, so in this case would I need to type all the 55 QuestionID and all the possible answers for each QuestionID?

PossibleAnswerID QuestionID PossibleResponse

1 1 Always
2 1 Sometimes
3 1 Rarely
4 1 Never
1 2 Always
2 2 Sometimes
3 2 Rarely
4 2 Never
. .
. .
1 55 Always
2 55 Sometimes
Would this be correct?


and one more thing. The AnswerID in the table Answers for people with more than one response can be an autonumber field? I really appreciate all the time you have taken to break this down for me.

Carlos

 

[willir]

No. You can change this to meet your needs -- sorry that I missed this point earlier. If you will always choose from the same answer, you can simplify tblPossibleAnswers

tblPossibleAnswers
PossibleAnswerID - primary key
PossibleResponse - text

or even...
tblPossibleAnswers
PossibleResponse - primary key, text

...if the answers are always short. (Some would even suggest to not use a table, and use a value list instead. Personally, I prefer to use tables since maintenance is easier after someone else inherits your database.)

Richard

 

[carlosguill]

Thank you Richard for your ideas and recomendations, your help was very valuable. Thanks to every body else too for all the suggestions.