design problem - questionairre database

[tizwaz]

Can anyone advise me how to set up a database which is to hold the results of various questionairres.
There are 4 types of questionairre and each one has about 50 - 100 questions on it.
There are a number of paper versions of the questionaiire already completed and the person who needs the analyse the results would like to input the results into an access form which looks the same as the questionairre. ie name, age, dept, date of survey at top and then for each question a number, the question and a yes or no option box. They then want to get percentages of the yes answers for each question grouped by age, dept etc.

Someone had already started a database with each record consisting of name, age, dept, date,Q1,Q2, Q3 etc. this works to a point but to get the questions on the form (rather than just the question number) they have had to type them into labels. This seems rather an odd way to do it and time-consuming as the same will have to be done for reports. However I am having trouble thinking of a better way of doing it. Making question number a field in the results table with another table listing question number and question doesn't seem to work with what I need to do as I end up with 50 - 100 records for each questionairre ie one record for each question number

 

[KenReay]

Hi

I would suggest that one row per question is the correct way to do it, something like:

tblQuestions
Id Autonumber (PK)
QuestionNo
QuestionText
...etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dcurtis]

Check the following link. You are looking for "At Your Survey"

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

 

[tizwaz]

Thanks - I'll try and work from here. I can see what you're saying is right but will I be able to have the input form list all the available questions with Yes No option boxes for the user to input the answers?

 

[dhookom]

The "At Your Survey" application has this type of data entry form. You might not have to change any table structures, queries, forms,...

Come back with any question since I am the programmer of the application.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dcurtis]

I realize I didn't give Duane credit for the application in my post. Sorry Duane.

 

[tizwaz]

Sorry didn't get back sooner something came up. I have started to have a look at the At your survey sample and it does seem to offer something similar to what I need. Duane - which would be the essential tables to copy in order to do what I want - I think your example is probably more sophisticated than mine needs to be. I have 4 surveys all with a number of questions, all with a yes or no answer. The other info collected per survey is Name, date of survey, age band of person taking survey etc. The form you mention is very much the sort of thing I want but I'm not sure I follow how you've done it - is the part with the questions on a subform and if so what field are you linking it on? Also I see that the Enter Results button's on click event runs the following:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappNewResponses"
Me![sfrmResponses].Requery
DoCmd.SetWarnings True
and thatqappNewResponses is an append query but I'm not sure what it is appending to.

would be v grateful if you could explain how this works to me

 

[dhookom]

You could add FirstName, LastName,... to the table tblSrvRspns. I would leave the Age Band as a question.

The append query creates a new set of records related to tblSrvRspns for selecting the responses to the survey questions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tizwaz]

Thanks Duane - think I'm starting to get there - will post back if I run into any more problems.
As regards to having yes or no as my answer would you think a yes or no combo box would be best? I was thinking of an option group with 2 check boxes but that would give me numbers as my answers wouldn't it - would that be difficult to query?

 

[dhookom]

If every question will always be true/false then you could use an option group. You can set the True option to -1 and false to 0. This shouldn't cause any problem querying.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.