Report - How to compare year to year data

[patriciashoe]

I have a table that has a primary key consisting of three fields:

Buildingid
Year
Subjectid

The other fields in this table record the number of teachers in the grade and students enrolled.

Example

Buildingid year subjectid countteachers enrollstudents
1 2008 12 5 100
1 2007 12 6 125


Here is my problem. I need to produce a report that shows the increase or decrease in countteachers and enrollstudents from one year to another. The structure of the report requested is as follows:

The report would be grouped by building

Staffing

Subject id 2007 teacher 2007 enrollment 2008 teachers 2008 enrollment +/- teachers +/- students

I can’t envision how to do this unless all this data is available in one record. I have thought about writing some code that would create a temp table that could loop through a recordset and and basically create a table they way I need it. Any ideas would be most appreciated.

[Delerna]

Is this what you mean

SELECT a.BuildingID,a.Year,a.SubjectID,a.CountTeacher,
        a.SubjectID-b.SubjectID as DeltaSubjects,
        a.CountTeacher-b.CountTeacher as DeltaTeachers
FROM YourTable a
JOIN  Youtable b on a.Year=b.year+1
 

you may need to swap things around in the subtractions in order to get the result you are after.
I hope it helps

[patriciashoe]

Finally got this to work. Here is the answer: Thanks all!

SELECT Subjectid,
       SUM(IIF(Year=2007,CountTeachers,0)) AS [2007 Teacher],
       SUM(IIF(Year=2007,EnrollStudents,0)) AS [2007 Enrollment],
       SUM(IIF(Year=2008,CountTeachers,0)) AS [2008 Teacher],
       SUM(IIF(Year=2008,EnrollStudents,0)) AS [2008 Enrollment],
       SUM(IIF(Year=2008,CountTeachers,0)-IIF(Year=2007,CountTeachers,0)) AS [+/- Teachers],
       SUM(IIF(Year=2008,EnrollStudents,0)-IIF(Year=2007,EnrollStudents,0)) AS [+/- Students]
FROM yourtable
GROUP BY Subjectid

[NeoPa]

I would consider something like the following. Just a little bit tidier. I'm guessing you probably have a different table name, but I'll use [YourTable] as in the other examples as I don't know it ;) I'm also assuming that [Year] is a numeric field rather than a text one, again from previous examples.

SELECT SubjectID,
       Sum(IIf([Year]=2007,[CountTeachers],0)) AS [2007 Teachers],
       Sum(IIf([Year]=2007,[EnrollStudents],0)) AS [2007 Enrollments],
       Sum(IIf([Year]=2008,[CountTeachers],0)) AS [2008 Teachers],
       Sum(IIf([Year]=2008,[EnrollStudents],0)) AS [2008 Enrollments],
       ([2008 Teachers]-[2007 Teachers]) AS [+/- Teachers],
       ([2008 Enrollments]-[2007 Enrollments]) AS [+/- Students]
FROM [YourTable]
GROUP BY [SubjectID]

[patriciashoe]

THank you!! I like your tidy approach.

Patti

[NeoPa]

My pleasure.

I did consider mentioning that the years should be flexible (to save you having to amend it every year) but the request was for titles including the year.
You should possibly consider this. Let us know if you do and want assistance.