If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A report design question
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Total hours from Table into Report | HeatherLou1974 | General Discussion | 3 | March 7th, 2005 08:04 PM |
Set up a report using more than one query | jbeck2010 | Database Design | 6 | February 12th, 2005 06:59 AM |
question on using OLE objects in a report | TracyG | General Discussion | 0 | January 10th, 2005 04:03 AM |
Dynamic Page Margins | Vel | Setting Up & Running Reports | 6 | September 19th, 2004 09:46 PM |