Military Unit Database
in [Tables]
|
Prev: "Rewrite" an application without affecting old calculations
Next: Asset tracking design problem using MS Access 2007
From: Anthony on 2 Dec 2009 17:05 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
From: KARL DEWEY on 2 Dec 2009 18:43
>>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. >>tblMAL which tracks weapons and sensitive items with serial numbers as 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). >>tblRank which lists all military ranks with autonumber as PK (don't think I need the PK for this table) Use the PK instead of Rank/Grade. >>tblMOS which lists military occupational specialties assigned to my unit with autonumber as PK (same as above) Will you be including sub-specialist (I was a 32Z5TL7 at retirement, MSG with 26). Hope this helps some. -- Build a little, test a little. "Anthony" wrote: > 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 > . > |