Hi Patti. The solution I propose relies on several queries, not one. I prefer to break problems down into smaller segments, each of which can be tested and verified as working correctly before moving to the next.
The solution to what you have asked for works as follows:
1. calculate the total enrolments for all schools in the enrollment table by year
2. extract from the total enrolments the current and next year's enrolment totals together
3. compute the projected total enrolments for all schools using the formula
projected total = school total / current year total * next year total
4. Append the projected totals to the Teacherresource table for schools 12 and 13 only.
Note that I am assuming there is no entry for 2008-2009 in the table, and am appending a new row for that year and not updating an existing row. The projected totals query cannot itself be used to update the teacher resources table - the combination does not result in an updatable query. If you do need to update the rows the projected totals query should become a make table query creating a temporary table which in turn can be joined to teacherresources for the update. I can advise you further on this if you are not able to use the append version listed.
Although I have prepared a working solution I would suggest that if there are only two schools to update it would have been much simpler to do the calculations by hand (as you had to do for one of the two schools in preparing test data) and append the data manually...
Anyway, I append the queries in order below. You need to store them under the names given. It is the last of these which you run to append the projected totals for 2008-2009 to the teacherresources table.
I have had to make some assumptions about the correct field names to use, as these have valried in your posts (student_enroll vs strudent_enroll, for example).
- qryTotalEnrolments
-
SELECT Sum(Enrollment.Enroll_Data) AS [Total Enrolments], Enrollment.Year
-
FROM Enrollment
-
GROUP BY Enrollment.Year;
-
-
qryYearTotals
-
SELECT Current.[Total Enrolments] AS [Curr Total Enr], Next.[Total Enrolments] AS [Next Total Enr], Current.Year AS [Current Year], Next.Year AS [Next Year]
-
FROM qryTotalEnrolments AS [Current], qryTotalEnrolments AS [Next]
-
WHERE (((Val(Left([Next].[Year],4))=Val(Left([Current].[Year],4))+1)=True));
-
-
qryProjectedTotals
-
SELECT teacherresource.student_enroll, qryYearTotals.[Curr Total Enr], qryYearTotals.[Next Total Enr], qryYearTotals.[Next Year], teacherresource.trschool_id, Int([Student_Enroll]/[Curr Total Enr]*[Next Total Enr]) AS Projected
-
FROM teacherresource INNER JOIN qryYearTotals ON teacherresource.tryear = qryYearTotals.[Current Year];
-
-
qryAppendProjected
-
INSERT INTO teacherresource ( tryear, trschool_id, student_enroll )
-
SELECT qryProjectedTotals.[Next Year], qryProjectedTotals.trschool_id, qryProjectedTotals.Projected
-
FROM qryProjectedTotals
-
WHERE (((qryProjectedTotals.trschool_id)=12 Or (qryProjectedTotals.trschool_id)=13));
Sample data:
- Total Enrolments Year
-
1756 2007-2008
-
1662 2008-2009
-
-
Curr Total Enr Next Total Enr Current Year Next Year
-
1756 1662 2007-2008 2008-2009
-
-
student_enroll Curr Total Enr Next Total Enr Next Year trschool_id Projected
-
1353 1756 1662 2008-2009 13 1280
-
100 1756 1662 2008-2009 12 94
-
10 1756 1662 2008-2009 10 9
-
10 1756 1662 2008-2009 10 9
-
-
Next Year trschool_id Projected
-
2008-2009 13 1280
-
2008-2009 12 94
-Stewart
41
