So I am constructing a database for my military squadron that handles quite a few aspects of day to day operations. One area that I am struggling with is designing a section for a persons Deployment Folder. For those unfamiliar, a deployment folder is unique to each individual and contains all the required training an individual has to be able to deploy. Some of the training is regional specific, so what is good for the Middle East may not be needed for the Pacific or South America. As of right now I have two tables designed. One is just for Deployment Requirements. It contains things like the Requirement Name, what region it is needed for, how often you have to accomplish the task and what part of the folder it sits in. The second table is based on the individual. When the database is utilized, a person should be able to assign a training requirement to an individual and put in the date when it was accomplished. With this data I can run queries to determine whether or not a person has all the requirements necessary to go to a certain region. The issue I am running into is being sure not to create duplicate entries by mistake while also being able to update the Deployment Requirements table with future training that may come down from higher headquarters. I set the Index to Yes (No duplicates) but then it only lets me put in one Deployment Requirement per individual as opposed to the 40+ some I already have established.
Also the primary key for each table is ID Autonumber. It has to be this way because the back end is going on sharepoint which only recognizes ID Autonumber for primary keys.
Also the third table in the Pic is the central table that I use as the PK. It contains all the Personnel Info for each person in the unit (Name, Rank, SSN, Physical Fitness Test Dates, Performance Report Date, etc.)
Also I am new at Access and have been learning via Youtube so I am not overly familiar with terms. If you see a better way to construct I am all ears
-Carrinmere