Military Unit Database

[Anthony]

I am currently attempting to consilidate information from several
different areas (personal information, licensing, weapons, physical
fitness, Professional development schooling, unit training classes,
etc) for my Military Police Unit. The purpose of this database will
be to be able to access information quickly in the form of reports
which can be printed on a moment's notice to print address rosters, e-
mail rosters, weapons rosters, unit class training rosters, etc.
Currently I have the following tables:

tblSoldier which tracks soldier information with a AKO username as PK
tblTraining which tracks all training information with autonumber as
PK and AKO username as FK
tblMAL which tracks weapons and sensitive items with serial numbers as
PK and AKO username as FK
tblRank which lists all military ranks with autonumber as PK (don't
think I need the PK for this table)
tblMOS which lists military occupational specialties assigned to my
unit with autonumber as PK (same as above)

I have searched older messages in the group but did not find anything
that could really help me. I believe I am on the right track with
this so far but am a little stumped by how to keep track of the unit
training classes. What I want is the ability to choose a class and
print a report on that particular class that will tell me all of the
Soldiers who have received the training with the training date and all
those Soldiers who have not been trained. Now, all Soldiers will not
necessarily receive training on the same date. Because this will a
many to many relationship, I should have multiple tables, right? I
think that I should have the following tables to make this possible:

tblClasses which lists the unit training classes with Class ID as PK
tblAttendance which tracks attended unit classes with autonumber as
PK, class ID and AKO_username as FK

Am I on the right track? Thanks for the help.
SFC Anthony Saunders
342nd Military Police Company

 

[KARL DEWEY]

tblTraining which tracks all training information with autonumber as
PK and AKO username as FK
Training falls into at least two types
- one-time - Unit orientation, etc.
- recurring - First Aid, Weapons qualification/familiarization
-
You need to list the subject and qualification level in a table and the
scheduling/accomplishment history in another. In the subject table have a
field for recurrance interval (0 for one time) and another for period (M-
months, Q- quarter, etc.) and in query use DateAdd. You might also have
another field to control which date to base next training on - from last
scheduled date or last accomplished date - an IIF statement in the DateAdd
will do that.
Have an append query run immediately after entering training completion
dates to generate the next required training records.

PK and AKO username as FK
You need two tables - weapons/sensitive items and assignment history. Who
as assigned what, when, and when returned. All items need to be assigned to
someone - individual, Armorer, or support (with supporting receipts).
think I need the PK for this table)
Use the PK instead of Rank/Grade.
Will you be including sub-specialist (I was a 32Z5TL7 at retirement, MSG
with 26).

Hope this helps some.