Setting up a records archive..

[Steven Cappiello]

I have a database that is used to track job applicants. Each season, we have a "recruitment" period, twice per year. After each season, I'd like to be able to archive those applicants who we're no longer interested in... especially since one of the database's main functions is to show the current crop of applicants on various reports. However, we'd like to be able to pull up archived records at a moment's notice.

The two ways I can think of approaching this are:

1. create a separate "archive" database.. and write a "send to archive" script in the main database and a "restore" script in the archive database.

2. keep all records in the main database (I'm guessing we get less than 1000 applicants per season)

and then script all the menu & navigation buttons to ensure that the user stays within the current found set ? (but then I'd need something like SecureFM to make sure that they can't "Show All" records.) right ??

I guess option 1 seems more straightforward but my fear there is that I might add a field here and there over the course of the year.. and then when it comes time to archive, I'll have to remember what changes were made to the original database..

Is there a better way of approaching this ?

thanks