How would you do this?

[Guest]

I am creating a student progress report database. Our school is grades 5-8.
Each year, we get new 5th graders and the 8th graders leave to go to the high
school. Is there an easy way to update the student info table and forms based
on that to remove old data and put in the new without rebuilding all tables,
queries, forms and reports?

 

[Brendan Reynolds]

It would be easy enough to delete the eighth-graders records with a delete
query - assuming there is a field in the students table that records what
grade that student is in ...

DELETE * FROM students WHERE Grade = 8

.... where 'students' is the name of your students table, 'Grade' is the name
of the field in the students table, and 8 is whatever value in that field
indicates an eighth grader.

That's not what I would recommend, though. Instead of deleting those
records, I would include a 'DateLeft' field in the record. Then you can mark
those students as left by inserting a date into that field ...

UPDATE students SET DateLeft = Date() WHERE Grade = 8

Now you can easily separate students currently in the school from students
who have left by looking at the DateLeft field. Students currently in the
school ...

SELECT * FROM students WHERE DateLeft IS NULL

Students who have left ...

SELECT * FROM students WHERE DateLeft IS NOT NULL

Students who left last year ...

SELECT * FROM students WHERE Year([DateLeft]) = (Year(Date())-1)