I have a database which records students grades. There are several tables, but the main ones are Students and Grades (one Student to many Grades relationship via a unique student number).

When a pupil leaves school, I don't want them appearing in finds any more, but still need to be able to have their details available.

I thought of two ways of doing this:

1. Have a field which states whether a student is "current" or has "left school"

This could cause problems when searching, however.

2. When a pupil leaves, have a script which deletes their record from the Students table and pastes it into another archived "Leavers" table.

This can't be a new problem. Is there a standard method of doing this?

Stick with the first option. A Status field (Active/Inactive) can be indexed, which means searches on it are very quick. Having this Status field with a default value in your find ("Active") is fast and easy to script.

This also makes reenrolling a student a simple matter.

In my system, I use Start Date, Drop Date, Graduation Date, and a couple others to calculate a Status field, and then have a default search value Status = "Enrolled", where users can override it to search for another status.

I make an Archive File. This file has the same tables as the active file except the calculation fields are replaced by data fields. Using GTRR Show find the related records. Import them into the matching achive table. Then isolate the record(s) in the main table and import them into the matching archive table. Delete the records in the main table, if your relationship is set right it will also delete the related records.

An alternate to deleteing records is to mark them and the have your find routines skip the marked records.