Hi,
I am trying to design a database to track Soldiers' school request packets. Attached is what I have designed so far.
The general idea is that I have hundreds of Soldiers submitting school request packets to me, so I first created a table to track Soldier personal info (SoldierInfo Table). It has a unique key for each Soldier. Soldiers come and go so I'll always need to add new Soldiers as they arrive in the unit or submit a school request for the first time.
The Soldier submits a school request packet (hard copy) that request a certain school and class date, usually a primary and alternate class date. Since I need to track the status of the packet, I created another table for the packet with a unique key for each packet, using SoldierInfo key as a foreign key with One to Many relationship. I also put the SchoolInfo Key as a foreign key in the PacketInfo Table with a Many to One relationship.
I went on to build a SchoolInfo table to capture each individual school. Each school has a unique key. This key is a foreign key in the PacketInfo Table and a foreign key in the ClassInfo Table.
Lastly, each school is run several times a year so I build a table "ClassInfo," to show each class date for each session a school has throughout the year.
As tables and to capture the data, this design works fine. However I am having some serious issues when I try to imagine a new Soldier submits a new school request. Basic forms allow me to input the new soldier info or select a soldier I already input. The subform allows me to fill in new packet info as well. However, I am unable to view more sub-forms to select the school and class date.
My design may be flawed, which is why I am posting here. Essentially, I am trying to create an input form that allows me to select a soldier I already have info on or create a new one. Then I need to create a new packet on that soldier or update a previous one. Next, I need to select which school the packet is for or input a new school, if it's a school we don't normally deal with and I was not already tracking for some reason. Lastly, when I select a school, I need to select the class # or class dates, and it should only have options for class dates that are tied to the specific school.
Eventually, I want this database to feed into our SharePoint Calendar and populate it with all of the schools and dates, but I need to work through these basic problems first.
I'm still watching videos on Lynda.com and trying to learn as much as I can. Please email me if the file didn't upload correctly and I can send it to you. 350zflyby@gmail.com I am even open to exchanging phone numbers so I can get this worked out. Willing to pay a little $$ but I can't afford to much...
SchoolRequest.zip