Survey

[J. T via AccessMonster.com]

I am working on a survey that has 50 questions. Each question is a rating
system from 1 to 5. I am using the Lookup Wizard data type for each of the
survey question. I have separate columns (fields) for each question number,
similar to a spreadsheet. I have a column field for each question (50 column
fields in total), plus one for the person's name (the data type is text ).

There will be 20 people who will answer this survey. I am trying to figure
out how to design the database.

From this survey, I need several things. 1) I need to know what questions
did people give a certain score to. For instance, I want to who gave a score
of 1 to a question in the survey and what question was it.

In addition, I also would like to answer questions like this: how many people
answered question #1 with a rating of 3 or how many people answered question
#2 with a rating of 1.

Basically, I want to make a survey out of the 50 questions and show how
people responded to each question in a report. In one report, I want to show
that John used a rating of 1 on 12 questions of the survey. If this is
confusing let me know. I'll try to explain it better.

 

[KARL DEWEY]

I would use three tables --
People -
PeopleID - number - long interger
LName - text
FName - text
Other info - text

Question -
QuestionID - number - long interger
Question - text

Answers -
PeopleID - number - long interger
QuestionID - number - long interger
Answer - number - longer interger - >0 and <6

 

[Duane Hookom]

I would create a survey similar to "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There is
a simple crosstabulation query that creates a "matrix" of one question vs
another question.

You should be able to use this sample without changing a table, query, form,
report,...

 

[tina]

comments inline.

I am working on a survey that has 50 questions. Each question is a rating
system from 1 to 5. I am using the Lookup Wizard data type for each of the
survey question.

strongly recommend you do NOT use lookup wizards in your tables. see
http://www.mvps.org/access/lookupfields.htm for reasons why.

I have separate columns (fields) for each question number,
similar to a spreadsheet.

Access is not a spreadsheet, it's a relational data management tool; you
should structure your data relationally. to learn the basics of relational
data modeling, see Database Design for Mere Mortals by Michael Hernandez,
and the multiple resources listed at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

I have a column field for each question (50 column
fields in total), plus one for the person's name (the data type is text ).

There will be 20 people who will answer this survey. I am trying to figure
out how to design the database.

see comments above re relational data modeling. defining and building your
tables/relationship is the first and the most crucial step in building any
relational database, so you'll want to make sure you do it right before
moving on to queries/forms/reports.

From this survey, I need several things. 1) I need to know what questions
did people give a certain score to. For instance, I want to who gave a score
of 1 to a question in the survey and what question was it.

In addition, I also would like to answer questions like this: how many people
answered question #1 with a rating of 3 or how many people answered question
#2 with a rating of 1.

Basically, I want to make a survey out of the 50 questions and show how
people responded to each question in a report. In one report, I want to show
that John used a rating of 1 on 12 questions of the survey. If this is
confusing let me know. I'll try to explain it better.

*in addition to* the recommendations above, suggest you take a look at MVP
Duane Hookom's survey database, which you can download from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
keep in mind that you need to learn basic relational data modeling
principles, so that you can understand the how and *why* of Duane's demo.
then you'll also be prepared to modify it to suit your own needs - or simply
use it as a reference while building your own db from scratch.

hth

 

[J. T via AccessMonster.com]

Thank you for all your advice so far. I am using different methods to get
this survey done. I understand that the setting up the information from the
beginning is more important. I just need a little more guidance on this
topic.

The situation is the same as before; I am trying to set up a 52 question
survey (The number of questions have been increased) for twenty people to
fill out. There are 50 multiple choice rating questions (Rating from 1 to 5.
1 =highest, 2 =good 3 =acceptable, 4=needs improvement, 5=not applicable).

There is also one question for the person's name (text) and one question that
might use radio buttons or checkmarks. I may also need a comment area for
each respondent.

1. Even though there are 50 different multiple choice rating questions in
this survey, the answers available for these questions will be the same. For
example, answers available in question #1 will be the same answers available
for question # 10. Do I have to re-write the answers for each question? Is it
true for the AYS2000 system as well.

2. Another advice I have received was to make three seperate tables. After
making the tables, what do I have to do? In addition, as i mentioned in
question #1, Do I have to re-write the answers for each question?

I greatly appreciate any advice or methods you can share. Thank you JT
P.S. I am not using Lookup Wizard.

 

[tina]

comments inline.

Thank you for all your advice so far. I am using different methods to get
this survey done. I understand that the setting up the information from the
beginning is more important. I just need a little more guidance on this
topic.

The situation is the same as before; I am trying to set up a 52 question
survey (The number of questions have been increased) for twenty people to
fill out. There are 50 multiple choice rating questions (Rating from 1 to 5.
1 =highest, 2 =good 3 =acceptable, 4=needs improvement, 5=not applicable).

as you see, the number of questions has already changed - and you haven't
even built the db yet. that's why you need a relational design, rather than
the flat file (one "column" per question) design you described in your first
post.

There is also one question for the person's name (text) and one question that
might use radio buttons or checkmarks. I may also need a comment area for
each respondent.

1. Even though there are 50 different multiple choice rating questions in
this survey, the answers available for these questions will be the same. For
example, answers available in question #1 will be the same answers available
for question # 10. Do I have to re-write the answers for each question? Is it
true for the AYS2000 system as well.

if you set up the database relationally, you only need to enter the
"available answer choices" once, in one table. then record the answer that
each user chooses for each question. as for the "AYS2000 system" question, i
don't know what you're referring to, so i can't comment.

2. Another advice I have received was to make three seperate tables. After
making the tables, what do I have to do? In addition, as i mentioned in
question #1, Do I have to re-write the answers for each question?

based on the info you posted, i would probably have a minimum of five tables
in the database. but making your design decisions based on the comments of
somebody who doesn't have a complete picture of the survey process and
requirements, is a bad idea. you're involved in the process, or at least
have access to it. so you're in a better position to analyze the process,
and design the tables/relationships to support it, than anybody in the
newsgroups.

as for what to do after designing and building the tables/relationships -
you have to build the queries, forms, and reports that provide the means to
enter data into the database and get meaningful information out of it. if
you've never built a database in Access before, one very good text that can
guide you through the steps to building a functional database is Microsoft
Access <version> Bible by Prague and Irwin. you can always come back to the
newsgroups with specific questions, but this is not a forum where you can
learn how to build an entire database.

I greatly appreciate any advice or methods you can share. Thank you JT
P.S. I am not using Lookup Wizard.

good, JT, glad you're not putting Lookup fields in your tables. as for
advice, i can only refer you to the above, and back to my previous post.
Access is not easy to learn to use, even to build basic databases - at least
not in the way that Word or Excel are. it takes an investment of time and
effort, beginning with basic data modeling principles, as i said before. and
a survey database is not a very simple undertaking; if you don't build it
right, you can't depend on the validity of the statistical data you get from
it.

also suggest you re-read Duane's post elsewhere in this thread. you may find
that his database provides everything you need without modifications. but
keep in mind that if it doesn't, you need to follow the learning path above
in order to understand how to modify the db to suit your needs.

hth

 

[Duane Hookom]

At Your Survey doesn't have a built-in method to use the same list of
possible responses for each question. I just didn't add this level of
complexity that defined a list of possible answers that might be applied to
one or more questions.

When viewing one list of possible responses to a question, you can select
them all and copy them to the clipboard. These values can then be
paste/appended as responses to another question. You could also modify the
application to use the same set of responses to every question.

 

[J. T via AccessMonster.com]

Another matter has come up.

While using reading the instructions for "At Your Survey", I saw that there
was a button to delete the other sample surveys. However, when I downloaded
the program, I do not see that button. Is it possible to delete the other
surveys while keeping the one that I will be working on?

Thank you

 

[Duane Hookom]

You should be able to open the survey table and delete the survey records. I
believe I set up the relationships to cascade deletes.