Another Survey/Questionnaire Question!

Another Survey Question

I am designing a database that stores results for medical record audits.
Each record is scored using 52 criteria with possible answer: Yes, No, or Not Applicable.
The final score is calculated on a formula that I will have in a calculated field in a query.

I have searched this forum and found some very excellent threads and samples about surveys/questionnaires (thanks Pat Hartman and others!)

They seem to indicate the best approach is not to have all the results in one table (all the answers) but rather use multiple tables like this:

tblMRreviews
MedicalRecordReviewID
MrreviewDate
ProvID
PtID
PtDOB

tblQuestions
QuestionID
MedicalRecordReviewID
QuestionText

tblOption
OptionID
OptionText

tblScores
ScoreID
QuestionID
OptionID

My problem is I just cannot get my mind around how this works.
I have downloaded and studied and printed the relationship structure and table designs but am still stuck.

Does anyone have a sample of something like a simple survey/questionnaire that has a form/queries that I could look at?

Or other suggestions?
Thank you!

The odds are that what works for Joe Schmo won't work for you. Everyone has a different twist. At a place where I once worked many moons ago, we had a leak-detection algorithm for oil & gas pipelines. After we told our customers about it, we implemented it for them. When our 24 major customers were finished, we had 25 algorithms. Theirs and ours.

When doing this kind of survey, the important factor is to recognize that you have two choices, one of which is to do it excruciatingly right but it might take a while; or to do it quick and dirty, which will be quicker to come up but harder to modify down the road.

If you look at your stated problem, one thing stands out that differs from other questionnaires we have seen. Your range of answers is highly limited. Just yes, no, or n/a. That is an option group right there. If you have 52 option groups that EVERYONE fills out and this will never change, take the low road. Do a quick and dirty design with 52 option-group slots.

Perhaps two tables - one for the review ID and one for the questions, then a joining table that exists for the junction of the Review ID and question number. You could take the quick and dirty approximation that every question will be asked. So when you create a new record, do an append query that adds 52 new entries in your junction table, consisting of the review ID and each question and a default answer of n/a. Then go back, open up a subform to the junction table selecting on review ID as the link between parent and child forms. Now go back in the subform to check the yes or no option groups. Anything you skip is n/a anyway, right?

NOTE: The ONLY reason I suggest this quick and dirty method is because you have a constrained range of answers. I have discussed a similar question with another person where their answers were all over the map. If your answers can change at a later time, expanding to a wider range or even generalizing, you might not wish to make this simplifying assumption.

Thanks Doc for your reply.

You are correct: Every question has the same choices and will never change and all of them must be answered. I agree with the option group idea and that is what I have planned to use.

My original idea was to create a table like this:
ID
Answer1
Answer2
Answer3
Answer4
.....
Answer52

where every record represents an audit and all the scores.

After reading this forum, looking at samples and reading your advice, it seems I should be looking at something that would have the tables you mentioned and the junction table where each record is one answer?

Is this on the right track?

I guess I have been thinking in a spreadsheet mindset with the first design. Your way is foreign to me and will take me a while to get a grasp of it. I will work on your suggestions even tho at this point I am still not clear.
It's not the training I have had or not had, I was just born dense!

Thanks again for your reply!

The questions are stored in one table, the answers in another. The following is the absolutely simplest solution. Your solution will be more complex if you have multiple surveys or the same survey may be taken more than once.

tblQuestions
QuestionID (autonumber primary key)
QuestionText

tblAnswerCodes
AnswerID (autonumber primary key)
AnswerText

tblPersonAnswers
PersonID (primary key fld1 - foreign key to tblPerson)
QuestionID (primary key fld2 - foreign key to tblQuestions)
AnswerID (foreign key to tblAnswerCodes)

The questions are defined in tblQuestions. You have 52 questions so there would be 52 ROWS! in this table.
The answer codes are defined in tblAnswerCodes. You have 3 possible values so there would be 3 rows in this table.
The survey takers answers are stored in tblPersonAnswers. This table has a two field primary key - the person's ID and the question's ID. There will be 52 ROWS for EACH person in this table.

Thanks Pat, per usual, your answer is clear and precise and helpful. I was able to make a start using both Doc's and your replies.

The only question I have left is: would you agree with Doc'x suggestion regarding using the append query to add 52 records to the tblPersonAnswers table upon adding each new survey. I have begun using this and it is working fine but thought I would ask your opinion in case there is another way of going about this. (No offense to the Doc Man!! )

ANd if it means anything regarding this particular question, all 52 questions must be answered for each survey.

Either way, thank you both all the help!! I was really stuck for a while.

Yes, that is how I would do it. Since answers to all questions are required, I would also create a query that counts the number of null answers by respondent and run this query when the db opens or on demand. You can then inform the user that some surveys are incomplete. There is nothing you can really do as the person is entering the survey data so don't even worry about it. You could run the count query in the unload event of the form to warn the user that he hasn't finished. The unload event is cancellable which is why you would use it rather than the close event should you want to prevent the form from closing.

Thanks Pat...good ideas.....apparently Bridge players really do know all the tricks!

Bridge, Access (programming in general), and driving a vehicle all have many things in common. The most important being planning. Make sure you know where you are at all times and where you want to go and don't forget Murphy's law - If something can go wrong, it will!

Form design?

This is probably a really dumb question - but it isn't coming to me!, how do the actual forms used to fill in the questionaire work?

I am happy with the table structure, and I think I am Ok with the questioner adding a survey- with all its questions and possible answers and the forms needed to allow this.


But how do I display my questionaire and save the results to a table.


My best guess so far - probably way off the mark is - initial screen chooses which questionaire is to be completed and by whom, this then runs an append query to the persons answers table - then have a form with the persons answers table as the recordsource?

So the personId and questionId are already complete - it is just the answerID to be added.

How do I allow this - single forms with a combobox to choose the answers and on form current the combo box is requeried ro get the correct answers set for each question??

Also how would I go about adding Free text questions to a survey, rather than a question with a finite set of answers??

Thanks for any help - my apologies again if my question is really dumb!