I need help with a concept

[Dave]

I have a survey that populates an Access DB.

There are 7 questions with the possibilities of a 1 2 3 or 4 answer.

I want to write a query that counts each of the 1s 2s 3s or 4s as well at
the total count of each question.

Example: there are 80 responses to Q1 answer 1 has 10, 2 has 15, 3 has 50
and 4 has 5.

I know that I can write a lot of queries, but I am hoping that there is an
easier way than write a query for each possibility.

Thanks

 

[Ted Allen]

Hi Dave,

This could be very easy or very hard, depending on your
table structure. If you have a table structure such that
you have one field for question number, and another for
the answer, then you can just use a totals query to group
by question number and answer number, and also count
either field. Finally, if you want to see the total
number of responses for each question as well, you could
use a simple correlated subquery or the DCount() function.

But, before I go through all of the steps of doing this,
please post back with some info on your table structure
to see if this method will work.

HTH, Ted Allen

 

[Dave]

First I can still change the table if necessary.

The table "Results" fields are:
EmplID - Each employee taking the survey
Questions by number: example Q1 through Q7 (within each question, example,
Q1 is the answer 1, 2 ,3 or 4

Thanks for the response

Dave

 

[Ted Allen]

Hi Dave,

If I understand your post correctly, you currently have
separate columns (fields) for each question number,
similar to what would be laid out on a spreadsheet. This
type of layout will be very difficult to work with down
the road, and will not be very flexible for future
surveys (if that will be a consideration).

A much preferred way of setting it up would be to have a
table that has a field for Employee ID, Survey ID (if you
will ever have other surveys), Question No, and Answer.

You would then have a related table listing all employees
(and if you were doing large numbers of surveys you would
also likely have lookup tables for surveys and question
numbers and maybe even answer ranges, but for now it
sounds like you don't need that).

Having your data structured this way, such that all
question numbers are in a single field and their
corresponding answers in another field, makes it very
easy to group by answer numbers and count results. It
also makes it fairly easy to do slightly more advanced
calculations and expressions such as your desire to
return the total number of records for each question
number, without having to write expressions for every
single question number.

I recommend a table structure something like the
following:

TblEmployees
EmployeeID (AutoNumber)
LastName (Text)
FirstName (Text)
....Any other employee Data you may need.

TblAnswers
EmployeeIDRef (Long Integer)
QuestionNo (Byte or Integer)
Answer (Probably Byte - depends on your range)
....You may also need SurveyNo if you will be doing
multiple surveys.

Then, in the relationships window define a relationship
between the EmployeeID and the EmployeeIDRef. You
probably should double click the relationship and check
enforce referential integrity to prevent invalid
EmployeeID entries.

In TblAnswers for the EmployeeIDRef field you can use the
lookup properties to specify TblEmployees as the lookup
source to give you a drop-down list of employees (set the
number of columns = three to display the names)

This structure will make your life much easier down the
road. If you get into looking at doing a lot of surveys,
I know that I have seen some of the MVP's post links to
sample survey databases that show recommended ways to
handle surveys. You could probably find these by
searching for survey, but post back if you can't and I
can try to find one (if you are interested).

Once you have this structure you can try my earlier
recommendation for the query setup, but post back if you
need help.

-Ted Allen