SQL for Access Report

[EAGER BEAVER]

Can anyone provide me with the SQL so that I can generate a report quickly?

I need some help for a basic Access Report that I intend to distribute to
staff after developing a summary of the survey.
Basic Information
Table Name: Influenza Clinic Survey
First Column Name: Question 1
2nd Column Name: Question 2
Etc, Etc.
There are 60 Questions in the survey. And I need to get the total number of
responses for each question without listing each record. Responses can be
"Yes", No, Other, Unknown. All the responses are in a text format.

I am especially naive when it comes to Access and if you have the patience,
I'd appreciate your help.
Thanks much.

 

[Kipp Woodard]

This approach would work:

SELECT "Question1Answer" AS Question,
[Influenza Clinic Survey].Question1Answer AS Answer,
Count([Influenza Clinic Survey].Question1Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question1Answer", [Influenza Clinic Survey].Question1Answer;

Union All
SELECT "Question2Answer" AS Question,
[Influenza Clinic Survey].Question2Answer AS Answer,
Count([Influenza Clinic Survey].Question2Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question2Answer", [Influenza Clinic Survey].Question2Answer;

Union All
SELECT "Question3Answer" AS Question,
[Influenza Clinic Survey].Question3Answer AS Answer,
Count([Influenza Clinic Survey].Question3Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question3Answer", [Influenza Clinic Survey].Question3Answer;

Union All
SELECT "Question4Answer" AS Question,
[Influenza Clinic Survey].Question4Answer AS Answer,
Count([Influenza Clinic Survey].Question4Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question4Answer", [Influenza Clinic Survey].Question4Answer;

Union All
SELECT "Question5Answer" AS Question,
[Influenza Clinic Survey].Question5Answer AS Answer,
Count([Influenza Clinic Survey].Question5Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question5Answer", [Influenza Clinic Survey].Question5Answer;

Union All
SELECT "Question6Answer" AS Question,
[Influenza Clinic Survey].Question6Answer AS Answer,
Count([Influenza Clinic Survey].Question6Answer) AS [Count]
FROM [Influenza Clinic Survey]
GROUP BY "Question6Answer", [Influenza Clinic Survey].Question6Answer;

etc....

However, it would be cleaner if you redefined the survey table to hold a
record for each table. It would look like this:

CREATE TABLE InfluenzaClinicSurvey (
SurveyID INTEGER NOT NULL ,
ResponseID INTEGER NOT NULL ,
QuestionID INTEGER NOT NULL ,
Response VARCHAR(15) NOT NULL ,
PRIMARY KEY([SurveyID], [ResponseID], [QuestionID])
)

Then your query would be much simpler:

SELECT InfluenzaClinicSurvey.SurveyID, InfluenzaClinicSurvey.QuestionID,
InfluenzaClinicSurvey.Response, Count(InfluenzaClinicSurvey.Response) AS
CountOfResponse
FROM InfluenzaClinicSurvey
GROUP BY InfluenzaClinicSurvey.SurveyID, InfluenzaClinicSurvey.QuestionID,
InfluenzaClinicSurvey.Response;

I would also have a tables for Surveys, Responses, and Questions. Each
would have two columns, an ID, a unique key, and a description, to describe
the record for the particular ID. You could then join these tables as
needed to produce the queries that you want.