HOw to UPDATE programatically

[patriciashoe]

Good Morning

I have a table as noted below.

Schoolid num
Gradeid Num
TeacherFTE Num
Student_enroll Num
year text

The primary key is made up of gradeid, schoolid and year. The application loads two years worth of data. For example, for each record in 2008 there is another for 2009. Several of the schools require that a formula be applied to predict the next year's enrollment. My question is how to construct an UPDATE for just these schools. In other words, the enrollments for year 2008 for schools A and B need to be updated so that a formula is applied and the result of the formula is updated to the matching student _enroll records for the year 2009.

Data example
Schoolid Student enroll year gradeid
12 1003 2008 17
12 2004 2008 18
12 1123 2008 23

The records for the next year exist with 0 enrollments, I need a way to update the table so that I can update the student_enroll field for the year 2009 after applying a formula to the 2008 student_enroll number. Thanks for getting me started.

Patti

[dbpros]

I would probably do this with a VBA recordset function.

You can use recordsets with criteria to select the desired records to get your calculations.

You can then use recordsets and the rs.Edit and rs.Update commands to update the desired records.

Hope this helps

http://www.db-pros.com