Count responses in report or Query

[Clay]

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

 

[John Spencer]

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
'====================================================

 

[Clay]

John here is the sturcture of my tables:

Dept.
Q1
Q2
Q3

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

 

[John Spencer]

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
'====================================================

 

[Clay]

Thank you John.