dm******@hotmail.com wrote:
Example:
1. Position
2. Speciality
3. Authorized (<-- This is the column that would have more than one)
At first review, these three types of information should probably be in
three separate tables. For example:
The following table lists the various unit positions.
Table: TBL_POSITION
Fields:
POS_PK (an autonumber - make this the primary key)
POS_NAME (required)
POS_DESCRIPTION (a more detailed description, perhaps)
This next table lists the possible specialties, MOS/AOC (I don't know
what these are - when I was a tank officer in the Canadian army, we
referred to this as "MOC classification" for officers, or "trade" for
non-commissioned troops, plus there's also the possibility of
considering civilian) that are the minimum requirement for this
position. You could possibly have this information as part of the
position information in TBL_POSITION, above, but it occurs to me that
differing branches/MOS could have different requirements if the job is
available to more than just one trade/officer classification.
8) For example, it is common knowledge that armoured soldiers are vastly
superior to anyone in any military. A lowly armour 2Lt could do the job
of an infantry brigadier with one hand tied behind his/her back, for
example, so you might want to indicate rank/grade/whatever of whatever
MOC/Classification/Trades that can fill the position, so you'll want to
have a separate table for this:
Table: TBL_POSITION_REQUIREMENTS
Fields:
PRE_PK (autonumber - make the primary key)
PRE_POS_FK (refers to the POS_PK in the TBL_POSITION table above. Index
this, allowing duplicates)
PRE_MOS_AOC_CODE
PRE_MIN_GRADE (refers to rank)
PRE_NOTES
FInally, you need a table of the the personnel who are filling the
position(s).
Table TBL_POSITION_AUTHORIZED
Fields:
POA_PK (autonumber - make the primary key)
POA_POS_FK (refers to the POS_PK in the TBL_POSITION table above. Index
this, allowing duplicates)
POA_MOS_AOC_CODE (the ACTUAL MOS/AOC of the individual occupying the
position. This can be useful for comparing whose over/under qualified
for the job, etc)
POA_GRADE (rank - see notes on POA_MOS_AOC_CODE)
POA_FIRST_NAME
POA_LAST_NAME
POA_OTHER_NAME
POA_DATE_EFFECTIVE
POA_DATE_LEFT (the date the person left this position - this is good to
be able to get a history of people who've occupied the position and by
specifying something like POA_DATE_LEFT = NULL you'll get people who are
currently in this position)
POA_NOTES
The above is how a database should be used. A query bringing these
three tables together will give you the records for the report you want.
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
