Help with advanced query

[patriciashoe]

I am trying to determine if I can create a select query that will produce the following data for a report. I would like a report/query that is organized as follows:

<Grade/SubjectName> <Sum of Teacher FTE> <Sum of Enroll> (by school_id)

Report would like like this.

Grade  School A  School B
  1    4    100  5    125
  2     ………

It seems to me I should be able to create this using iif statements or case statements.
Here is my table structure. Thanks so much....Patti

[B]Table = GradeLevels[/B]
[I]FieldName;         Type;       Index[/I]
grade_subjectName  Text
grade_subjectID;   Long Integer; PK
displayorder;      Text
level_school;      Text
staffingDivisor;   Single
internal_notes;    Text
glnotes;           Text
staffing_add;      Long Integer

[B]Table = Schools[/B]
[I]FieldName;         Type;       Index[/I]
schoollName;       Text
school_id;         Long Integer  PK
principal;         Text
Type;              Text
sixth_grade_enrol; Long Integer

[B]Table = TeacherResources[/B]
[I]FieldName;       Type;        Index[/I]
TeacherFTE;      Double
trschool_id;     Long Integer;  PK
grade_subjectid; Long Integer;  PK
tryear;          Text;          PK
Student_enroll;  Double
Displayorder;    Text
Notes;           Text

[NeoPa]

To allow the school info to spread across the page you will probably want to use a crosstab query. I deal with this first as it throws up various issues. How many schools to allow for in the design of the report? A situation is created where the design of the report is tied to data in a table (not driven by - but tied to). This is highly recommended against and is likely to cause you problems in future (or before).

Using IIf() it is possible to produce similar results, but with the same provisos.

The SQL for the basic data (I don't do crosstabs I'm afraid) would be something like :

SELECT tGL.Grade_SubjectName,
       tS.SchoolName,
       Sum(tTR.TeacherFTE) AS SumTeacherFTE,
       Sum(tTR.Student_Enroll) AS SumStudent_Enroll
FROM ([TeacherResources] AS tTR LEFT JOIN
     [Schools] AS tS
  ON tTR.TRSchool_ID=tS.School_ID) LEFT JOIN
     [GradeLevels] AS tGL
  ON tTR.Grade_SubjectID=tGL.Grade_SubjectID
GROUP BY tTR.Grade_SubjectID,
         tTR.TRSchool_ID

To build another query on top of this one (using this as a data source) make it a SQL Subquery (See Subqueries in SQL).