SQL Group Values in Reports

[dom24]

Hi,

I have the following fields in a table:

CourseName, Q1, Q2, Q3, Q4.....Q19.

I want to display, in a report, each course name followed by the answers to the questions in a row, i.e.:

Course Name: Assertiveness

Q1 Q2 Q3

A B B
B A C
C B A
D D A
A B B
B C A

Etc...........

The code i'm using at the moment is :

SELECT DISTINCT dbo_Course.CourseName, dbo_Validation.Validationq1, dbo_Validation.Validationq2, dbo_Validation.Validationq3, dbo_Validation.Validationq4, dbo_Validation.Validationq5, dbo_Validation.Validationq6, dbo_Validation.Validationq7, dbo_Validation.Validationq8, dbo_Validation.Validationq9, dbo_Validation.Validationq10, dbo_Validation.Validationq11, dbo_Validation.Validationq12, dbo_Validation.Validationq13, dbo_Validation.Validationq14, dbo_Validation.Validationq15, dbo_Validation.Validationq16, dbo_Validation.Validationq17, dbo_Validation.Validationq18, dbo_Validation.Validationq19
FROM dbo_Validation INNER JOIN dbo_Course ON dbo_Validation.CourseKey = dbo_Course.CourseKey
WHERE (((dbo_Course.CourseName) In ('ABC Flowcharter','Access 97 Course','Assertiveness')))
ORDER BY dbo_Course.CourseName;

AT the moment when a course is repeated in the table but with different answers i'm getting the course name displayed followed by the answer, but then it's repated again followed by the different answers.

Help!

 

[dhookom]

Are you using the Sorting and Grouping dialog within the report design view?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dom24]

Errrr..........nope! Didn't know there was one, it's all new to me........

 

[dhookom]

Sorting and Grouping in report design allows you to set the order you want records to appear. You can also choose to display a group header section which you would use for the Course Name.

BTW: since "it looks like you are creating a survey" you might want to review At Your Survey found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.