A report design question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

I'm trying to print out the results of a survey. My tables were not
normalized, so after a crosstab query, I'm able to get these as columns:
QuestionNo
SurveyDate
Excellent
Good
Unsatisfactory
Total

Excellent, Good, Unsatisfactory and Total represent the total number of
Excellent, Good or Unsatisfactory responses for each question. Total is the
total number of responses for each question.

What I would like is a report that looks like this:
Nov04 Dec04 Jan 04 ...
Question# 1
Excellent 15 13 17
Good 2 5 3
Unsatis. 1 0 2
Total 18 18 22
Question# 2
Excellent 13 16 11
Good 3 2 1
Unsatis. 1 0 2
Total 17 18 14
...

I'm having tough time with this. Could anyone guide me in the right
direction? Any help is greatly appreciated.
 
Normalize, Normalize, Normalize,...

First create a UNION query:

SELECT QuestionNo, SurveyDate, "Excellent" as Response, [Excellent] As NumOf
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Good", [Good]
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Unsatisfactory", [Unsatisfactory]
FROM qYourQuery

Then create a crosstab from the union query that has QuestionNo and Response
as the Row Headings, some form of your SurveyDate as the Column Heading, and
Sum of NumOf as the Value.
 
Thank you very much Duane,

I will normalize that's for sure. But there's one thing that bothers me
about the output. If there are no Unsatisfactory answers for that month for
question 5 let's say, the output doesn't have Unsatisfactory for that month.
What I'd rather see is:

Question 5
Excellent 10
Good 3
Unsatis. 0
Total 13

Is that possible? Thanks again for your help.

Duane Hookom said:
Normalize, Normalize, Normalize,...

First create a UNION query:

SELECT QuestionNo, SurveyDate, "Excellent" as Response, [Excellent] As NumOf
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Good", [Good]
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Unsatisfactory", [Unsatisfactory]
FROM qYourQuery

Then create a crosstab from the union query that has QuestionNo and Response
as the Row Headings, some form of your SurveyDate as the Column Heading, and
Sum of NumOf as the Value.

--
Duane Hookom
MS Access MVP


Al said:
Hello everyone,

I'm trying to print out the results of a survey. My tables were not
normalized, so after a crosstab query, I'm able to get these as columns:
QuestionNo
SurveyDate
Excellent
Good
Unsatisfactory
Total

Excellent, Good, Unsatisfactory and Total represent the total number of
Excellent, Good or Unsatisfactory responses for each question. Total is
the
total number of responses for each question.

What I would like is a report that looks like this:
Nov04 Dec04 Jan 04 ...
Question# 1
Excellent 15 13 17
Good 2 5 3
Unsatis. 1 0 2
Total 18 18 22
Question# 2
Excellent 13 16 11
Good 3 2 1
Unsatis. 1 0 2
Total 17 18 14
..

I'm having tough time with this. Could anyone guide me in the right
direction? Any help is greatly appreciated.
Click to expand...
Click to expand...
 
You would need to have a table with every possible rating that could be
joined to your query using a left or right join.

--
Duane Hookom
MS Access MVP
--

Al said:
Thank you very much Duane,

I will normalize that's for sure. But there's one thing that bothers me
about the output. If there are no Unsatisfactory answers for that month
for
question 5 let's say, the output doesn't have Unsatisfactory for that
month.
What I'd rather see is:

Question 5
Excellent 10
Good 3
Unsatis. 0
Total 13

Is that possible? Thanks again for your help.

Duane Hookom said:
Normalize, Normalize, Normalize,...

First create a UNION query:

SELECT QuestionNo, SurveyDate, "Excellent" as Response, [Excellent] As
NumOf
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Good", [Good]
FROM qYourQuery
UNION ALL
SELECT QuestionNo, SurveyDate, "Unsatisfactory", [Unsatisfactory]
FROM qYourQuery

Then create a crosstab from the union query that has QuestionNo and
Response
as the Row Headings, some form of your SurveyDate as the Column Heading,
and
Sum of NumOf as the Value.

--
Duane Hookom
MS Access MVP


Al said:
Hello everyone,

I'm trying to print out the results of a survey. My tables were not
normalized, so after a crosstab query, I'm able to get these as
columns:
QuestionNo
SurveyDate
Excellent
Good
Unsatisfactory
Total

Excellent, Good, Unsatisfactory and Total represent the total number of
Excellent, Good or Unsatisfactory responses for each question. Total is
the
total number of responses for each question.

What I would like is a report that looks like this:
Nov04 Dec04 Jan 04 ...
Question# 1
Excellent 15 13 17
Good 2 5 3
Unsatis. 1 0 2
Total 18 18 22
Question# 2
Excellent 13 16 11
Good 3 2 1
Unsatis. 1 0 2
Total 17 18 14
..

I'm having tough time with this. Could anyone guide me in the right
direction? Any help is greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top