Simple Questionnaire DB

[Pixie78]

I'm just looking for a simple database to input the results of a
questionnaire/survey done by our employees. It was made in Excel but printed
and handed out to the employees. There are 32 questions and the possible
answers for each on are:

1. Strongly Disagree
2. Disagree
3. Neither Agree or Disagree
4. Agree
5. Strongly Agree

I'm not looking for anything fancy and I'm not looking to create these
surveys in the database or anything like that. That's not my job function.
I did get the task of having to tally up the results of this survey and there
are others once in a while that are similar to it so it would be handy to
have this little interface to zip through the tally process.
I just wanted to see if there was an easy way to set up a form similar to
the survey taken and run through it survey by survey and then run a totals
query for the results. A tally, how many strongly agrees for question 1, 2 ,
3, so on and so forth. The survey's are anonymous so all I have are the
questions and the answers. I looked up the At Your Survey download and it
just seems way beyond what I'm trying to accomplish.
Anyone have any suggestions?? Thanks in advance

 

[scubadiver]

Just set up a single table with the 32 questions and an autonumber. If it is
a regular survey then you will need an indicator for each survey group and
what about demographics? age, gender and whatnot.

Then you can do your averages, distributions, cross-tabs, filtered forms,
graphs.

 

[Michael Gramelspacher]

I'm just looking for a simple database to input the results of a
questionnaire/survey done by our employees. It was made in Excel but printed
and handed out to the employees. There are 32 questions and the possible
answers for each on are:

1. Strongly Disagree
2. Disagree
3. Neither Agree or Disagree
4. Agree
5. Strongly Agree

I'm not looking for anything fancy and I'm not looking to create these
surveys in the database or anything like that. That's not my job function.
I did get the task of having to tally up the results of this survey and there
are others once in a while that are similar to it so it would be handy to
have this little interface to zip through the tally process.
I just wanted to see if there was an easy way to set up a form similar to
the survey taken and run through it survey by survey and then run a totals
query for the results. A tally, how many strongly agrees for question 1, 2 ,
3, so on and so forth. The survey's are anonymous so all I have are the
questions and the answers. I looked up the At Your Survey download and it
just seems way beyond what I'm trying to accomplish.
Anyone have any suggestions?? Thanks in advance

this is a quick example which may or may not work for you
http://www.psci.net/gramelsp/temp/SurveyExample1.zip

 

[scubadiver]

I think you will need 34 fields. A field for each edition of the survey (if
it is to be repeated monthly, quarterly), a field that identifies each
questionnaire within each survey set and the remaining 32 for each question.

To speed up the data entry put this in the "on change" event of the first box:

If Len(Me![FIELD1].Text) > 1 Then
Me![FIELD2].SetFocus
End If

When you enter a single digit it will set the focus automatically to the
next text box.

Remember to change your fields names appropriately when you move to the next
text box.

 

[John W. Vinson]

I think you will need 34 fields. A field for each edition of the survey (if
it is to be repeated monthly, quarterly), a field that identifies each
questionnaire within each survey set and the remaining 32 for each question.

BZZZZT!!!

"Pull over to the side. Take your hands off the keyboard and keep them in
sight. You're under arrest for Committing Spreadsheet!"

<g>

Seriously, this is a very common error in designing surveys. You're much
better off having a one-to-many relationship from Surveys to Questions, and
from Surveys to Answers; instead of 32 fields, you would have 32 records, one
for each question. You can use a Crosstab query to *display* a grid layout,
and even use a Grid control to edit the answers - but the tables should be
tall and thin, not storing questions in fieldnames.

John W. Vinson [MVP]

 

[Keith Wilby]

I'm just looking for a simple database to input the results of a
questionnaire/survey done by our employees. It was made in Excel but
printed
and handed out to the employees. There are 32 questions and the possible
answers for each on are:

I have one that you might be able to adapt. Drop me a line at keith <dot>
wilby <at> baesystems.com and I'll send you a copy (A2003).

Keith.

 

[scubadiver]

Sorry officer but I feel I haven't broken the law in this instance. For ease
of data entry it would be far easier to simply treat the questions as fields.

Pixie doesn't state how many surveys have been completed or how often the
survey will be repeated. What if there are 500 surveys or 16000 data points.
If I understand it right having questions as records would require selecting
the question in a combo box and then entering the response. A lot of very
time consuming work dont you think? Or have I missed a point somewhere.

Having said that Pixie may be better off considering using a web based portal.

 

[tina]

If I understand it right having questions as records would require
selecting

the question in a combo box and then entering the response.

not necessary. it's very easy to pre-fill a form with "answer" records where
the question reference is already entered, with the actual question being
displayed, and all the user has to do is run down the list, entering the
answers.

as John says, a short wide structure should absolutely be avoided. even
though the Op says this is a "simple" survey, it will most likely be
repeated at some point - and sure as God made little apples, somebody in
management will want to put in new questions. easy to handle in a correctly
normalized db, a nightmare in a short, wide structure.

hth

 

[scubadiver]

Am I thinking that questions can be entered into a table and then use it for
a query that can then be used as the source for the form?

 

[tina]

um, not quite. keep in mind that any table storing "answers" must include a
reference to a specific question in each record. it's just a matter of
populating the answers table "all at once" for each parent survey record,
then entering the answers manually from the paper media.

here's an example of how i use this setup daily in my job. i have to enter
test values in a database. each "batch" of material is put through several
different tests, and the test value of each test must be recorded for each
batch. so i have a table listing all the tests (a supporting table). i have
a parent table to enter each batch, by batch number, date issued, material
type, etc. i have a child table to hold each test value for each batch
record, linked to the pk of the batch table, of course. the pk from the
tests table is used as a foreign key in this child table.

my data entry setup: mainform, bound to the batches table, subform bound to
the child test values table. when i enter a new batch record in the
mainform, code runs to automatically append a record into the child table
for each batch test listed in the supporting table. the append query appends
the pk value from the batches table and the pk value from the tests table,
into the foreign key fields in each record. then the subform is requeried.
so now it contains an "answer" record for each test belonging to the
mainform batch record, though the test value field is blank in all the
records, of course. all i have to do is tab into the subform and Enter or
Tab from record to record, typing in the appropriate test value in each
record. i use a combo box control in the subform, with RowSource the tests
table, to display the "text" test name. the combo is disabled and locked
because i never choose a test manually - they're always preloaded.

if i delete the batch record in the main form, the child test value records
automatically get deleted of course. i also use code to check the child
records before leaving the subform; if none of the subform records has an
answer (test value) recorded, then all of the subform records related to the
current mainform record are deleted.

for my specific needs, i went a step further and included code to "re-load"
the test answers records for the current batch record in the main form, so i
can delete some or all of them from the subform if necessary, and then
re-run the append query to re-add the missing records. there are also some
"grouped" tests where there are two or more possible tests but i will enter
a test value for only one of the group, and i have more code that
automatically deletes the "un-used" test record(s) when i exit the subform.

you can get about as creative as needed for this kind of setup, but for the
op's data entry purposes i think a simple append query would probably be
sufficient - no need to get fancy!

hth

 

[scubadiver]

I think I understand what your set up is but how do you deal with the
foreign key field in the subform when you append the data. Do you set a
criteria in the append query.

Would it be possible for you to send a copy of yours for me to look at?
empty and compacted of course. I am intrigued.

david(dot)bowman(at)thames water(dot)co(dot)uk

 

[tina]

the setup in my db is part of a much larger, complex interface, it would
take me some time to pare it down to basics. but i built a demo of this very
concept some time back, for someone who posted; i'll see if i can dig it up
over the weekend and email it to you, scuba.

 

[scubadiver]

most things I can deal with but not survey design. I didn't think Access
would be a suitable method.

post it to here instead

david (at) cedar1 (dot) demon (dot) co (dot) uk

thanks

 

[tina]

hmm, okay, i hope by "post" you mean to "email" it to the address you gave?
at any rate, that's what i did, so i hope it gets to you okay. if not, post
back, and i'll send it from my other email. tina

 

[scubadiver]

I may have received it but Norton might have treated it as spam. I didn't
notice it in my inbox. Can you resend, cheers

 

[tina]

okay, on its' way again. hopefully i'm entering your email address right;
haven't got the dreaded "failure notice" email YET, so...

 

[Pixie78]

Sorry, I've been out of town. This sounds more like what I need. Could you
elaborate a little more? I'm understanding the concept and the end result,
just need a boost with getting there.
I didn't expect so much commotion over a survey =)
Here's the deal on the surveys: I don't care to set up the actual survey in
the database, they are just handed to me after the employees fill them out
and my boss says 'Here, go ahead and tally these up, thanks'. Then he sends
me an electronic copy of the survey (excel spreadsheet) and I enter the
totals on there and mail it back. Pretty simple. Except for the tally part,
that's time consuming reading each check mark. I don't care about naming the
questions, answers or surveys it's just for tally purposes and would probably
be used over and over for different surveys of the same set up. I'm still
new at my job so I don't know how often the same survey goes out but I know
the same TYPE of survey frequently goes out and if I'm the tally girl, I'd
like an easier way to get there. =)
Thanks so much!

 

[scubadiver]

Tina helped me out brilliantly when she sent me a demo of what she did. The
vast majority of responses are quantitative and the "answer" field is set to
number. Here is my current table design.

tble_survey
SurveyID (PK)
Quarter
Year

tble_resp
surveyID (FK)
RespID (PK)
D1
D2
D3

tble_answer
QID (FK)
RespID (FK)
AnswerID (PK)
Answer

tble_questions
SectionID (FK)
QID (PK)
QOrder
Question

tble_section
SectionID (PK)
Name

Questions

1) D1,D2,D3 are demographics. Should they stay there or go into
"tbl_questions"

2) I do have some text answers (like open comments). Should [answer] be
changed to a text field or will this cause problems?

3) How can I deal with a question that can have multiple responses?

Help would be appreciated.

Cheers

 

[scubadiver]

"I don't care to set up the actual survey in the database". What do you mean
by that? In order to enter information you need to set up tables and forms.

You don't have to enter the totals by hand. Export the summary information
to a source spreadsheet and link the cell references to another spreadsheet
in a format that your boss will like.

"tally" ho!

 

[Pixie78]

I mean that what I want is nothing fancy, I just want it to add for me. I
don't want to type in all the questions or create a pretty form with all
kinds of buttons and functions and spend a ton of time on this. I just want
a quick way to tally. Nothing more. The 2 tables I have so far are:

tblQuestions
QuestionID (PK) Numbered 1-32 in this case
Questions - probably contain a couple of words of the question in case I'm
tallying a survey that doesn't have actual numbered questions

tblAnswers
AnswerID (PK) Answer
1 Strongly Disagree
2 Disagree
3 Neither Agree or Disagree
4 Agree
5 Strongly Agree

There's no survey versions, demographics, names, departments or anything
else aside from the survey questions and answers and a bunch of check marks
in the boxes. There are just under 200 employees here but if the survey
isn't mandatory, there could only be 70 turned in. All our surveys are
similar with this opinion of Agree or Disagree.
I just want to have a form that has all the questions with the answer box
next to it and just tab through and type the answers as in 3 >tab 1 >tab 1

tab 4 >tab and when I get to question 32 I hit submit or send or it just

automatically goes to a new, blank survey form and I enter the results of the
next person and so on til there's no more and then run the query and see the
totals for each question. How many Agrees, Disagrees.... you get the point.
I know I need another table of SurveyID which will act as the number of
papers I have and then a table of the questions with the answers in which to
run the crosstab query, it's there I'm having the problem. How do I go about
linking the questions with a selectable answer and have each "form" equal the
32 questions I have? Make sense?