Ned advanced Query to build rpeort

[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:

Gradesubjectname Sum of Teacher FTE Sum of Enroll by school_id

Report would like like this.

School A School B
Grade
Grade 1 4 100 5 125
Grade 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

gradelevels
grade_subjectName Text
(PK) grade_subjectID Long Integer
displayorder Text
level_school Text
staffingDivisor Single
internal_notes Text
glnotes Text
staffing_add Long Integer

schools
schoollName Text
(PK) school_id Long Integer
principal Text
Type Text
sixth_grade_enrol Long Integer

TeacherResources
TeacherFTE Double
(PK) trschool_id Long Integer
(PK) grade_subjectid Long Integer
(PK) tryear Text
Student_enroll Double
Displayorder Text
Notes Text

 

[fneily]

From my viewpoint, maybe you can clarify a few things first.
From the school table, what does "sixth_grade_enrol" mean? Also, I would have a tblPrincipal table with PrincipalID, other info fields. Principals get transferred around, change names when married, etc. Also your field means the first and last name are in one field. So PrincipalID would be used in the Schools table.

GradeLevel table: what is "displayorder"? Also, this is used in another table. Should only be used once.
By it's name "level_school" should be in the school table.
What is "staffingDivisor" and "staffing_add"

TeacherResources: what is "TeacherFTE"? Does this table show how many students are in a grade/subject by school and term for a particular teacher? Again, Displayorder shows up.

For your report, you want to know how many students are enrolled in a subject and how many teachers teach that subject per school?

Also, are you keeping any info on the teachers or just a count?

 

[PatriciaShoe]

Thanks for the help. SIxth_grade_enroll is a work around for a complicated formula problem. It is used sparingly. The principals here do not get transfered - this is bascially static data. Displayorder is used to make sure the grades print out in an order that I have determined as opposed to alpha. Level_school indicates for each grade listed what type of school it is found in, i.e. elementary,middle or high schools. THis lets me find data by type of school as opposed to a particular school. Staffing_divisor ties into a function that makes computations based on a various formulas to find average class size. Staffing_add needs to be deleted.

This application does not track teachers as names - just counts. TeacherFTE is the count of teachers teaching a particular grade/course. I need to report these counts as well as student enrollments for each grade by school as noted in my original post.

Thanks for any help. Much appreciated.

Patti

 

[BigRed1212]

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


What is this output in English?

In grade 1 there are 4 teachers and 100 students?

 

[PatriciaShoe]

Yes, in grade 1 there are 4 teachers and 100 students.

Steve

 

[fneily]

Some notes: I'll assume that teachers are also not transferred around, like the principals.
Level_school is not needed since "type" is in tblSchools.
Is "sixth_grade_enrol" known for every school? If not, then being a purist, I would have a small table, tblSGE, that has fields SGEID, Sixth_grade_enrol. But that's up to you.

So, based on what you posted, these are my tables:
tblSchool with SchoolID, SchoolName, Type, PrincipalID, SGE
tblPrincipal with PrincipalID, firsname, lastname, etc.
tblSGE with SGEID, sixth_grade_enrol. (up to you)
tblGradeLevel with grade_subID, grade_subname, displayorder, internal_notes, glnotes, staffing_divisor
tblteacherresources with SchoolID, tryear, grade_subID, teacherFTE, students_enrolled, notes

For tblteacherresources primary key would be multi field schoolID, tryear, grade_subID.

Now a simple query connecting the tables will produce the reports you want such as:
SchoolName, PrincipalName, SubjectName, NumberOfTeachers, NumberOfStudents.
Or whatever you want to show.

That's if I understood everything.

 

[PatriciaShoe]

Actually, that is how I have the teacherresource table set up including the primary key. I am entering data and producing a number of reports. My difficulty is determining how to produce the report I initially mentioned. I want to produce a report by school type, easy enough. The report will group on each grade for that type of school detailing the teacherFTE and enrollment. Essentially I am trying to create a crosstab type report, however crosstabs will only give you one total column. I need two. So back to my original problem. can you point me in the direction of how to construct a report that would appear as follows:

School A School B School C
fte enroll
grade 1 4 100 5 110 4 100
Grade 2
Grade 3


Thanks for any insight and the help thus far. Patti & Steve

 

[fneily]

See:
How can I get a crosstab to have more than one value
faq701-4524

How to create a crosstab query with multiple value fields

http://support.microsoft.com/kb/304458