Group by Field in a Report

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

Guest

I'd like to create a report that displays responses from several survey
participants. Ideally the report would look like this:

Q1

1st Survey Respondent's Name 1st Survey Respondent's Response to Q1
2nd Survey Respondent's Name 2nd Survey Respondents Response to Q1
3rd Survey Respondent's Name 3rd Survey Respondent's Response to Q1
 
This would be quite simple if your tables were normalized like the At Your
Survey demo found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You can normalize your structure with a union query

SELECT RespName, 1 as Question, [Q1] as Response
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 2, [Q2]
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 3, [Q3]
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 4, [Q4]
FROM tblNoNameGiven
UNION ALL
--- etc ---
FROM tblNoNameGiven;

You can then create a query based on the union query.
 
Thanks a lot, Duane. It works (I have to brush off my limited SQL knowledge
and start using it again).

Duane Hookom said:
This would be quite simple if your tables were normalized like the At Your
Survey demo found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You can normalize your structure with a union query

SELECT RespName, 1 as Question, [Q1] as Response
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 2, [Q2]
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 3, [Q3]
FROM tblNoNameGiven
UNION ALL
SELECT RespName, 4, [Q4]
FROM tblNoNameGiven
UNION ALL
--- etc ---
FROM tblNoNameGiven;

You can then create a query based on the union query.

--
Duane Hookom
MS Access MVP
--

Chad Borman said:
I'd like to create a report that displays responses from several survey
participants. Ideally the report would look like this:

Q1

1st Survey Respondent's Name 1st Survey Respondent's Response to Q1
2nd Survey Respondent's Name 2nd Survey Respondents Response to Q1
3rd Survey Respondent's Name 3rd Survey Respondent's Response to Q1
.
.
.
.
.
.
nth Survey Respondent's Name nth Survey Respondent's Response to Q1


Q2

1st Survey Respondent's Name 1st Survey Respondent's Response to Q2
2nd Survey Respondent's Name 2nd Survey Respondents Response to Q2
3rd Survey Respondent's Name 3rd Survey Respondent's Response to Q2
.
.
.
.
.
.
nth Survey Respondent's Name nth Survey Respondent's Response to Q2


and so on. I have a field for each response (i.e., all reponses to Q1 are
in one field, all responses to Q2 are in another field) and a field for
the
survey respondent's name).

I can't find anything in the documentation that addresses this. I would
think some sort of 'grouping' would work, but I haven't been able to
figure
it out. Any help?

Thanks.
Click to expand...
Click to expand...
 
Back
Top