Multiple columns report grouped by one field

[marysilvaramos]

Hi to All:

Happy New Year!

I guess somebody else have had this issue before. A report needs to be created for a school it should print like this:

Student Name
.............. Period 1. Period2 . Period3.. Period 4
Subject1 Gradeq1 Gradeq2 Gradeq3 Gradeq4
Subject2 Gradeq1 Gradeq2 Gradeq3 Gradeq4

Currently it prints like this:

Student Name
Period 1
Subject1 Gradeq1
Subject2 Gradeq2

Period 2
Subject1 Gradeq1
Subject2 Gradeq2

Groups created are Student Name and Period

Thanks in advance

Mary

 

[apr pillai]

If you can maintain Period and Grade values in numerical form you can solve your issue with a Cross-tab Query like the sample given below:

TRANSFORM Sum(Students2.Grade) AS SumOfGrade
SELECT Students2.Student, Students2.Subject
FROM Students2
GROUP BY Students2.Student, Students2.Subject
PIVOT "Period" & [Period];

The data and cross-tab query output images are attached for info.

 

[marysilvaramos]

Thanks Apr Pillai:

It worked wonderful! Now, the report should include other fields like teacher name per subject, grade per subject (in letters: A, B, C, etc), teacher comments per subject. How can they be included in the query?

Thanks again,

Mary

 

[apr pillai]

Teacher Name & Teacher Comment columns can be added in the same way as Student Name & Subject.

Turning Grade values into A,B,C etc. is somewhat tricky. Grade 1 = A, 2 = B, 3 = C etc. right?

Use the following modified Crosstab Query SQL, replacing the earlier one:

TRANSFORM Sum([Grade]+64) AS Expr2
SELECT Students2.Student, Students2.Subject, Students2.Teacher, Students2.Comment
FROM Students2
GROUP BY Students2.Student, Students2.Subject, Students2.Teacher, Students2.Comment
PIVOT "Period" & [Period];

Create a Make-Table Query using the Cross-tab query as source.

Sample SQL:

SELECT Query1.* INTO ReportTable
FROM Query1;

The ReportTable Grades will show now grade 65, 66, 67 for 1,2,3 etc.

Design Report using ReportTable and write the expression =Chr$([Period1]) in the first grade column in detail section.

Number 65 will change to A, number 66 will change to B and so on for grades.

Change other columns also in this way.

See the attached Report images in Design & Report Views.

Don't forget to change the Column Name Property Value period1 to period1x, period2 to period2x and so on.

 

[marysilvaramos]

Thanks for your reply, Apr Pillai: It is amazing!

There are some changes on the format. Attached is the file:
comments should be per subject and teacher per quarter,
the grade and percentage per subject and teacher per quarter should be included too.

Thanks again,

Mary

 

[marysilvaramos]

Sorry, the file did not upload. Here it is..

 

[apr pillai]

The sample report format you have attached looks like designed in Excel. You must plan your Access Report in such a way that all the details pertains to one record on one line fashion, like:

Class Name | Teacher | Grade | Percent | Conduct | Effort | . . .

Highlighted columns for Qrtr2 to Qrtr4 also to be included. Perhaps you may need 20 to 25 queries to complete the report. It is better if you create Process flow charts (indicating sources table/query, query name, target query/table name step by step so that you don't loose track of what you have already done and what to do next.

Creating complex Reports with one or two Queries will be difficult.

The best approach is to create a Report Table with required fields as shown above. Build Queries for Appending/Updating records from the source table to the report table records Quarter by Quarter. You may need to create several Queries/tables and sequence them in a Macro so that every time you run the Macro the Report can be recreated with changed data. Design the Report using the Report Table.

You may add a delete query at the begining of the macro to remove all the records from the report table to recreate the report from changed source data source.

 

[marysilvaramos]

Thanks for your reply! I figured out what you mentioned. I am running out of time. For now, I am trying a mail merge from access to word. There are some issues also to resolve doing it...

Thanks again,

Mary