Group Report

[2013user]

I have a survey that contains 5 questions. For each question additional comments can be provided. I would like to create a reprort that groups the questions and responses together. Currently the report I have groups by person. ( I have only popluated two of the five questions on to the report which has been uploaded).

[June7]

Why do Communication and Timeliness fields have the same caption?

Why do the 5 memo fields have the same caption?

Survey data is not normalized. Cannot set grouping. Don't even have grouping by person. The report is simply displaying records sequentially in the record entry order.

[2013user]

Great catch. I went in and updated the captions as such. How can I further normalize the data to report by question? I already have the list of persons in a seprate table.

[June7]

Normalized structure would be:

PersonID	QuestionID	Response	Comment
7	1	4
7	2	4
7	3	4
7	4	4
7	5	4
21	1	4
21	2	4
21	3	4
21	4	4	Wonderful services people here
21	5	4	Absolutely

Now report can use the QuestionID field to do grouping.

A UNION query could rearrange the data into normalized structure. There is no wizard or designer for UNION, must type in SQL view.

SELECT PersonID, 1 AS QuestionID, Communication AS Response, CommunicationComments AS Comment FROM tbl_Survey
UNION SELECT PersonID, 2, Timeliness, TimelinessComments FROM tbl_Survey
UNION SELECT PersonID, 3, Pertinent, PertinentComments FROM tbl_Survey
UNION SELECT PersonID, 4, Satisfaction, SatisfactionComments FROM tbl_Survey
UNION SELECT PersonID, 5, Recommendation, RecommendationComments FROM tbl_Survey;

[workuser]

So essentially I do not need to create seperate tables for all the different fields? Brilliant. Is there anyway to also add the person's name in the report. I tried adding tbl_Person.FirstandLastName to each line but that does not work.

[June7]

Use the UNION query in another query that joins to tbl_Person. Then base report on that query.

Otherwise, each line of the UNION would have to be a join statement.

SELECT PersonID, FirstandLastName, 1 AS QuestionID, Communication AS Response, CommunicationComments AS Comment FROM tbl_Person RIGHT JOIN tbl_Survey ON tbl_Person.PersonID = tbl_Survey.ID
UNION SELECT PersonID, FirstandLastName, 2, Timeliness, TimelinessComments FROM tbl_Survey INNER JOIN tbl_Survey ON tbl_Person.PersonID = tbl_Survey.ID
...

Since tbl_Survey has only key values for the responses, also need response table joined in.

Also need a table of the questions and join that table into the query or type each question as a value in the UNION:
SELECT PersonID, FirstandLastName, 1 AS QuestionID, "Communication is disseminated in professional manner?" AS Question, ...

Can build SELECT query with query designer then copy/paste SQL statement into the UNION.