A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Military Unit Database



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 11:05 PM posted to microsoft.public.access.tablesdbdesign
Anthony[_8_]
external usenet poster
 
Posts: 41
Default Military Unit Database

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
  #2  
Old December 3rd, 2009, 12:43 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Military Unit Database

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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.