Questionnaire Database

[Del_Piero_3]

Hi guys, I need help with setting up my tables and relationship for a questionnaire type of database.

Basically there are two questionnaires that are conducted; one is completed on the day of the treatment and another one after the treatment on a later date. Both questionnaires have 13 questions each but are different. The database is supposed to store this data.

First questionnaire also has the patient ID number, Age and patient telephone contact number recorded while the second questionnaire only needs the patient ID number and a date. The second questionnaire has some questions that have multiple values such as comments with a normal answer yes or no.

This is so far I’ve come up with:

tblePatients
PatientID
Age
TelephoneNo

tbleQuestions
QuestionID
QuestionNo
Question

tbleAnswers
AnswerID
QuestionID
PatientID
Answer

The problem I am having is that I am not sure how to distinguish the difference between the two questionnaires and where the date field supposed to go? I would appreciate your help, I have to make sure the structure is right before I proceed further.

Kind Regards

 

[KenHigg]

Did you search the forum for this topic?


ken

 

[Del_Piero_3]

Did you search the forum for this topic?

Yes Ken I did, I got about 28 results. Some of them were very identical to my prob and very helpful, but none of them gave me the answer or a idea for the last part of the prob.

 

[KenHigg]

tblQuestionaireType
QuestionaireType_recno, pk
QuestionaireType_type
QuestionaireType_data1
QuestionaireType_data2
...

tblQuestionaireQuestions
QuestionaireQuestions_recno, pk
QuestionaireType_id, fk
QuestionaireQuestions_data1
QuestionaireQuestions_data2
...

tblQuestionaireInstances
QuestionaireInstances_recno, pk
QuestionaireType, fk
QuestionaireInstances_data1
QuestionaireInstances_data2
...

tblQResponses
QResponse_recno, pk
QuestionaireQuestions, fk
QuestionaireInstances_id, fk
tblQResponses_data1
tblQResponses_data2
...

I think this will work.
???
ken

 

[KenHigg]

Del - Sorry for the delay, been on a Men's Christian workshop conference...

Per your pm:

I need to ask a question regarding ur answer - I am beginner. Is it ok if you can explain the tables and the fields to me - how they are suppose to function

tblQuestionaireType is where you would define each type of questionair. You say there are just two but I think it still would be best have this table. You would would put info here like questionair name, instructions, etc.

tblQuestionaireQuestions would contain the actual questions and would link back to tblQuestionaireType.

tblQuestionaireInstances Each record here would represent the header type info for an instance of a returned or completed questionaire. If you have 100 returned 'day of the treatment' questionairs, you would have 100 records. You could put stuff here like the date the questionair was returned, etc.

tblQResponses Would contain the actual question responses.

I'm not by any means claiming that is the best way to set it up, just possibly the way I would consider doing it...

???
ken

 

[Del_Piero_3]

Thanx Ken for the reply and for the help. I have attached a screen shot of my tables, fields and the relationship. I hope I've done it right.

 

[KenHigg]

Looks good. I forgot the patient id stuff...


ken

 

[Del_Piero_3]

Looks good. I forgot the patient id stuff...

ken

Thank you, your help is appreciated.

 

[antifashionpimp]

Hi there,

I've checked out this thread and most others about questionnaires and surveys, and must say that your idea on this one Ken is quite helpful. I've based my solution on this thread, thanks for the input.

My one question that I dunno how to solve, is what if the responses are either text, a yes/no or a number? See, I want to keep each response as a record in tblResponses. Is this what you mean by having fields
tblQResponses_data1
tblQResponses_data2

E.g. then tblQResponses_data1 will be for text, tblQResponses_data2 will be for a yes/no...this means that all records will have at least one empty field for either of the abovementioned fields.

I'm on the right track, I hope
Or not?

 

[KenHigg]

Hum... Been a while since I looked at this...

Actually, I think each record in tblResponses represent one returned survey. And..........

Hum... If this set up was going to be used to track multiple questionnaire types, say if next month you wanted track an entirely new questionnaire, then question one / fld 1 data type could change from a text response to a yes/no type....????

I think I would just set up all the response flds up as text and type in "yes" / "no".

Does this help?
ken

 

[batman1056]

I am trying to do somthing very similar,
I haev 4 projects, each project has up to 10 differnt outputs.
I am trying to create a similar database. I would like to have a tab section where tab1 would be Project No 11, tab2 Project No 32 etc.
So each tab is a specific project, then in that tab section only the questions (Y/N) that are specific to that tabulation page would show up.

___________
What happens is I get a form filled in by Joe Bloggs, he is then allocated against a specific project, he may fit into P11 and P32, so what I am trying to do is when I click on P11 I can choose (Y/N) whcih ones are linked to him (then I`ll count these later), then I can if needed click on another tab box and allocate him against another projects outputs.

Sorry if this sounds complicated but a similar questionaire database with a linked tab section would be greate, that way when I select a perosn I can see what projects they have been linked to.

Any ideas..?

I have created the above, and in the response section I had added Y/N option.

What is the best method to get this infomation together in a form?

 

[antifashionpimp]

Hi Ken,

I followed the tips from this thread and built up my table structure, making it nice and normalised. My table structure is similiar to that of that of Del Piero's previously in this post (screenshot)

Now I have data filled up in these tables, and want to actually make use of them now. I have a table which I think will do this:

tblAnswer
----------
AnswerID 'pk
SurveyInstanceID 'foreign key to tblSurveyInstances
QuestionID 'foreign key to tblQuestions
AnswerID


What must I actually do next to get useful information from it? I want to setup a query, that contains all People who filled out the questionnaire, and the repsonses they gave to each question. Then using this query, I will later make another query, to find out specifically a list of people who e.g. answered Option 2 at QuestionID = Married.

All my questions are different format, i.e. not only mulitple choice.

Hope I made it clear enough so that someone can help. Would be kind of you if you could.

Regards,
Jean

 

[antifashionpimp]

I made a crosstab query as follows:

TRANSFORM First(tblAnswers.AnswerText) AS FirstAnsw
SELECT tblSurveyInstances.PersonID AS PID
FROM tblQuestions INNER JOIN (tblSurveyInstances INNER JOIN tblAnswers ON tblSurveyInstances.SurveyInstanceID = tblAnswers.SurveyInstanceID) ON tblQuestions.QuestionID = tblAnswers.QuestionID
WHERE (((tblQuestions.SurveID)=1))
GROUP BY tblSurveyInstances.PersonID
PIVOT tblQuestions.QuestionID;

It now shows what I want 95%, and it will do for now. I guess this has gone off topic, so if anyone wants to follow what I did next, go here.

Jean