I'm working at a university in Mexico this summer as an internship. Perhaps part of the trouble that I'm having with Access is that I'm using a Spanish version here at the school so I sometimes have trouble understanding Microsoft's technical explanation for why I can't do things (though I often have trouble understanding the explanations in English too).
My boss wants me to organize dozens of Excel files into a database on Microsoft Access. She wants to be able to easily create reports on individual students, spread out over several years. The reports would indicate years enrolled, grades, financial aid, etc. I've created four tables so far (with the framework for easily recreating these tables for future years)....
Virtual Student 06-07
On Campus Student 06-07
Virtual Student 07-08
On Campus Student 07-08
Each table has the following fields:
ID Number (definitely different for each student)
School E-mail Address (definitely different for each student)
Grade Point Average
Class 1 Name
Class 1 Grade
Class 2 Name
Class 2 Grade
Class 3 Name
Class 3 Grade
Class 4 Name
Class 4 Grade
Class 5 Name
Class 5 Grade
Class 6 Name
Class 6 Grade
What do you recommend making the primary key in each table? Since the primary key needs to be something unique, I imagine school e-mail address would be an acceptable primary key. Can you have the same field be the primary key for all tables?
I want to make relationships between all tables in the future. For example, I want to link all the info from 06-07 virtual to 06-07 on campus, 07-08 virtual, 07-08 on campus (is this a bad idea?). The reason I want to do this is my boss wants to be able to retrieve the grades and list of classes one student has taken. Generally students will be only "on campus", or only "virtual", but in some cases a student might switch between the two, that's why I also want to have relationships between "virtual" and "on campus".
So, what's the best way to make relationships? Link the Student ID's to the other Student ID's in other tables? Or something else?
Should I "exhibit referential integrity"?
Should I click "actualize in cascading the relational fields"?
Thank you all very much,