Count responses in report or Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clay - 18 Apr 2008 17:46 GMT

I am trying to create a report that will count the total disctint responses
for each question in a report. Here is what I have a set of 486 responses
with 40 questions. Each question has a reposinse of Yes, NO, NA or
Correction. I want to create a report to where I can summarize the count of
each response by question. Addionally I want to be able to group by
Department. Cna nayone help?

Here is what i have tried:
=Count(IIF(RhymeBOYK>3,1,Null))/Count(STUID)

and the DCOUnt

Reply to this Message

John Spencer - 18 Apr 2008 17:57 GMT

What is the structure of your tables?

It may be possible to do this with a cross tab query, but the solution
depends on how your tables are set up. The simplest structure might be

TableQuestions
QID
QText

TableResponses
SurveyID
QID
Response

Then this query would give you the starting point to get the percentages
Transform Count(Response) as NumberOfResponses
SELECT QID, Count(Response) as TotalResponses
FROM TableResponses
GROUP BY QID
PIVOT Response

AGAIN, you have to tell us how you have organized the data in the tables.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> I am trying to create a report that will count the total disctint responses
> for each question in a report. Here is what I have a set of 486 responses
[quoted text clipped - 7 lines]
>
> and the DCOUnt

Reply to this Message

Clay - 18 Apr 2008 18:10 GMT

John here is the sturcture of my tables:

Dept.
Q1
Q2
Q3

Each quesion has the possible answer of Yes NO NA or Correction

> What is the structure of your tables?
>
[quoted text clipped - 37 lines]
> >
> > and the DCOUnt

Reply to this Message

John Spencer - 19 Apr 2008 20:32 GMT

OUCH! Wrong structure. Lots of tedious work to get the desired results.

Basic Query would look like
SELECT Dept
, Abs(Sum(Q1="Yes")) as Q1Yes,
, Abs(Sum(Q1="No")) as Q1No,
, Abs(Sum(Q1="N/A")) as Q1NA,
, Abs(Sum(Q1="Correction")) as Q1Correction,
, Count(Q1) as Q1TotalResponses

, Abs(Sum(Q2="Yes")) as Q2Yes,
, Abs(Sum(Q2="No")) as Q2No,
, Abs(Sum(Q2="N/A")) as Q2NA,
, Abs(Sum(Q2="Correction")) as Q2Correction,
, Count(Q2) as Q2TotalResponses

(Repeat 38 more times for the other 38 questions)
FROM [YOUR TABLE NAME]
GROUP BY Dept

You can probably JUST get in all the questions in the max 255 columns of
an Access query.

Good luck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> John here is the sturcture of my tables:
>
[quoted text clipped - 46 lines]
>>>
>>> and the DCOUnt

Reply to this Message

Clay - 21 Apr 2008 15:07 GMT

Thank you John.

Reply to this Message