Need help with database of college students, how to track a history?

[XenoZephyr]

I am a grad student assistant for a department at my college. I've been asked to convert a very crude and outdated and very poorly designed database (FilemakerPro) to an MS Access database.

We have the latest version of Access (MS Access 2010), it will be running on a mix of Windows XP (updating to Windows 7 at some point), Windows 7, and a Mac (recent OS, not sure which).

The database is simple and straightforward for the most part. The database just records each student, standard stuff: name, address, etc, also GRE Scores, previous GPA, etc and we record when they've passed key exams, candidacy, who their advisor is and when they've graduated (or switched programs). Currently their old database is flat, they are just using it as if it was a spreadsheet and it causes lots of issues.

I've got the basics set up in Access, but I am not sure (though I have some ideas) on how to do the 2 following things:

We have a Student Table, and a Faculty Table (which just has Faculty Full name as a field right now)

1)
We want to have a field that says who the student's current Advisor is AND we want to have a record of any previous Advisors in case they have switched Advisors in the past (and to be able to see when this switch occurred). Besides using this field to look up who someone's advisor is, we also use it to print out lists of Advisees for each of the Faculty Advisors each Fall.

I think this is the way to approach it, but I am very unsure, any suggestions are Greatly appreciated!

Make a new Table called Advisements with the following 3 fields
StudentID (links to StudentID field in Student table)
Faculty (links to FullName in the Faculty table)
AssignmentDate (Date when this student was assigned to this Faculty member)

Then either an autonumber ID field as primary key, or maybe select each of the 3 fields and combine them as one primary key? (not sure about that)

The idea here is that if someone changes Advisors, then we would add a new entry into the Advisement tables with that StudentID, the new Faculty name, and a new Date. Though I'm not sure how to set that up (adding a new entry instead of just changing the current one).

Am I on the right track here at all?


2)
Very similar to the other problem:
In our department, students can be in different programs (Mental Health, School Psychology, Counseling Services, Play Therapy, etc). They can also be in more than one program (Many MH students are in one of our certificate programs like Play Therapy for example).

Right now, the old database is set up like this, it has the following fields:

Cohort 1 (what year they entered the program)
Current Program
Degree
Decision
Accepted Cohort 1
Student Decision


Graduated
Graduation Date

Now, in case there are people in multiple programs, OR if a person switched programs, there are 3 other sets of identical fields (Cohort2, Cohort3, etc). This is an awful setup and we have to do many work arounds (mostly because none of us know anything about FilemakerPro) and it is very complicated for other Grad Assistants who do lack the computer knowledge that I have (and mine is outdated as well).

The Decision, Accepted Cohort 1, Student Decision, and possibly Graduated are all being combined into a "Status" field instead of how it currently is.

I believe what I need to do is another multi-relational table.
The Programs table would have
StudentID (the link to the Student table)
Cohort
Program
Degree
Status
Possibly separate Graduated and Graduation date

Then a student could have more than one program and if they switch from one program to another one (which happens often) then we could just put in the Status field "Withdrawn" or something like that.

We want to keep a history, which I believe this will do...

Is this approach correct?


Thank you for your time and assistance!

[June7]

Yes, to maintain historical records will need the additional tables.

Use form/subform arrangements for data entry.