I need help so bare with me for this long post (it read quickly)
I am an Army Officer working at a training center for Captains. Here Captains are enrolled in the Captains Career Course. Each fiscal year there are several classes scheduled. It is a 20 week class roughly. I want to build a database that can keep track of a bunch of information. I am going to do some VBA programming to populate the tables with data but there will also be manual entry. I am having trouble with the design portion of this database. I am trying to design it to make my job easier. I have a BS in Computer Science but only took 1 database course in MySQL but since this is for Government work I have to use Access since the army is not going to pay for this.
My current database design schema is attached but let me lay out the scenario first. (Its a screen shot because I have very limited access to the government computer I am using so I apologize)
Each class has a number of students in it and the class is uniquely identified by a number (001,002,003 etc). Each class is split into 2 groups called flows: A student is either in Alpha Flow, or Bravo Flow. There are also instructors: An instructor only teaches Alpha Flow or Bravo Flow and remains there for the 20 week duration of the course. I can uniquely identify a class+flow but I imagine I want classes and flows to be 2 separate tables in order to get all the students in a flow of a certain class or all the students in the class when I query.
So there are 2 instructors per class, one for Alpha and one for Bravo. In the fiscal year since there is more than one class going on an instructor can teach more than one flow in a year but will not teach both flows of a class. The classes do not start on the same dates they are separated by several weeks sometimes a month (they are staggered)
So I need to be able to generate reports on instructors, students and the classes and flows. Here is the tough part. And I need some people to verify that this next part is achievable with Microsoft Access and some VBA programming.
When I add a new class to the classes table I will input the start date in a field called start_date and I want it to fill in all the blocks of instruction (there is a different title for each day in the 20 week course) So that way I can see exactly what date a class starts on. I will have a table where I input the holidays for that fiscal year so that way the VBA or macro or whatever will check against that table and will also check if certain dates fall on a weekend so when it schedules it will fill the items in order from a VBA array into the table.
Essentially I am building a schedule table (I think). Since the order never changes for a class (and the 2 flows in that class are on the same schedule), I should be able to auto generate the schedule for the 20 weeks. Start Date + 1 is always the same, so is start date + 2 etc. I just need to skip weekends and holidays.
Can anyone add/change or offer their own design schema I really just need the relationships as well as primary and foreign keys. I have attached mine as a start but I don't know how good it is and if it will do what I want it to.
What table does the schedule go into. Each date should be a record for that class/flow. Since the schedule is by class I need to be able to query a class and then get 20 weeks worth of records that show the dates they are in class and the title of that days instruction theme. I am also using this to try and sort out conflicts. I have a limited number of classrooms and instructors so sometimes 2 will be scheduled in the same place on the same day. That is okay, this way I can generate reports to show my superiors we are under staffed and under resourced. I need to be able to show: Who (what class/flow), who the instructor is, where or the location, what date it is on.