Need help with a design that tracks history

[XenoZephyr]

Using MS Access 2010

We have an old outdated Filemaker Pro database to track college student status within our department on campus. We are attempting to make a new Access database and fix many of the problems in the old database.

The primary table within our new database is the Student table with mostly demographic data. The primary key is StudentID.

Keeping track of the student's status within any program is very difficult with the old database, so for the new database, this is what I've come up with, but I'd like to know if this is the proper way of handling what I am trying to do or not.

StudentTable


-StudentID
-Other fields

ProgramTable
-Program

StatusTable
-Status

ProgramStatusTable
-ID
-StudentID
-Program
-Status
-Date

The ProgramStatus Table is tied to the StudentTable through StudentID. It has a unique ID field which would be the primary key because each student can be in more than one program.

Now, what we'd like to do is not just list what the status of the student's progress is, but to also keep a history.
Currently, if a student is in our School Psychology program and switches to Mental Health Counseling, then we change their program from SP to MHC, mark another field that indicated a program change occurred, and then reference it in a notes field. Instead, in the Access database, we want to leave it in that a student was accepted into SP at a certain date, then as a new entry we would say that they switched out of SP, and then as a third entry it would say that they are current in MHC. Does this make any sense? I have very basic database skills, and have never done any sort of history tracking. Would like to know if I'm setting this up correctly or not? One of the issues is then, how to set this up in a form later. If we just use a subform, someone could just change the data that is currently in the ProgramStatusTable instead of creating a new entry couldn't they?

Anyway, any suggestions, tips, links to references for this type of setup, would be MUCH appreciated.

Thanks!

[June7]

I am not sure you need the entry showing the switch out of SP. It could be understood that if another acceptance record follows the SP acceptance, then they must have withdrawn from the first before acceptance to the second.

Existing data could be changed via the form unless you set the form DataEntry property to Yes. This will cause the form to open only to a new record.

[orange]

I would question that FileMaker Pro is the issue. It may very well be part of the problem, but I recommend you get a meaningful, current data model to represent the business before getting too deep into Access.

Experience suggests that database software and complexity issues can often be traced back to a poor match between the tables and relationships and the business facts. Get your model created and tested before moving too quickly to usage/roll out.