Struggling with Unique/Complex Sorting on Report

[tonetl]

Greetings,

I've got a database that's essentially the results of a feedback survey given to students. The students selected their grade, selected their teacher, and then selected the course. After, they were asked to respond to essay type questions. Each record in the database is a submission from that survey. For the purpose of this example, let's assume the fields are uniqueID, studentGrade, Educator, Course, Essay1, and Essay2. I'd like to create a report that essentially groups by Educator first, then studentGrade, then Course, then Essay1, and then Essay2. So, assuming I have a science teacher who teaches grades 4 and 5 and has two difference science classes in grade 4, the final report would be structured as such:

Mrs. Smith

Grade 4

Intro to Science

Essay1: What could I do better?

Response to essay 1 from first submission
Response to essay 1 from second submission
Response to essay 1 from third submission
etc

Essay2: What topics would you like me to cover further in the future?

Response to essay 2 from first submission
Response to essay 2 from second submission
Response to essay 2 from third submission
etc

Advanced Grade 4 Science

Essay1: Some random question?

Response to essay 1 from first submission
Response to essay 1 from second submission

Essay2: Another random question?

Response to essay 2 from first submission


Response to essay 2 from second submission

Grade 5

Grade 5 Science

Essay1: Random question for grade 5 science?

Response to essay 1 for grade 5 science

Another response to essay 1 for grade 5 science

Essay2: Another random question for grade 5 science?

Response to essay question 2 for grade 5 science
Another response to question 2 for grade 5 science

I have been able to use the grouping options to group by educator, grade, and then course, but then when I add in Essay 1 and 2 the whole report falls apart. Essentially, the report looks as though it is just printing out essay 1 followed by 2 followed by 1 followed by 2 over and over again. I think it's doing this because the text for that field is different in each row. It seems as though the reporting tool groups by matching words/phrases. I'm completely dumbfounded on how to get the report to turn out the way I'd like.

Side note: I'm strong in VBA and fully anticipate needing to write some code to accomplish this. If you think that's the correct solution, could you at least point me in the right direction to get started with that (i.e. should I be writing a query? should it be a report? should I abandon this and move to Excel with VBA?).

[June7]

What is meant by first, second, third - each student?

You are using the report Grouping & Sorting features, not trying to group within query?

I don't think you will get the Essay1 and Essay2 responses to order that way because these two fields are not normalized structure. They can sit side-by-side but one below the other will not result in two groupings. It is just the two fields for each record.

A UNION query can manipulate the two fields into a normalized structure. There is no wizard or builder for UNION, must type or copy/paste in SQL view of query designer.

SELECT uniqueID, studentGrade, Educator, Course, "Essay1" AS EssayCode, Essay1 AS Response FROM tablename
UNION SELECT uniqueID, studentGrade, Educator, Course, "Essay2", Essay2 FROM tablename;

This provides a field called EssayCode that can be used in the report as another grouping level.

[tonetl]

What is meant by first, second, third - each student?

The first, second, third, etc is each submission from each student. Each submission is a row in the table. For the first essay question, I would like to show column D's value from row 1, column D's value from row 2, column D's value from row 3, etc until all of the responses are show....THEN move on to the next question, which would then show column E's value from row 1, column E's value from row 2, etc etc etc.

You are using the report Grouping & Sorting features, not trying to group within query?

Honestly, I hadn't thought about whether grouping via a query would help put the data in a better format for reporting than the current query that I have. I'll have to think deeply on that one.

I don't think you will get the Essay1 and Essay2 responses to order that way because these two fields are not normalized structure. They can sit side-by-side but one below the other will not result in two groupings. It is just the two fields for each record.

That's exactly what I suspected was going on, but I wasn't sure.

A UNION query can manipulate the two fields into a normalized structure. There is no wizard or builder for UNION, must type or copy/paste in SQL view of query designer.

SELECT uniqueID, studentGrade, Educator, Course, "Essay1" AS EssayCode, Essay1 AS Response FROM tablename
UNION SELECT uniqueID, studentGrade, Educator, Course, "Essay2", Essay2 FROM tablename;

This provides a field called EssayCode that can be used in the report as another grouping level.

I've used Union queries in previous projects. I'll play around and see if I can get the data in a more presentable way. Thanks for all of your suggestions.