Application Overview & Initial Design Proposal

                Delta Company, of the 300th Engineering Battalion, is an engineering unit within the US Army that specializes in “horizontal” engineering projects. This means that the unit can do any ground-level construction project with workspaces that can be reached by a scissor lift platform

                If the unit is tasked to work on projects with workspaces at much higher elevations, the Company Commander may approve involvement if safety equipment training is provided for high-elevation work or if the selected unit personnel are not required to expose themselves to working at high elevations on the work site. 

                Some of the projects that unit has completed include ground-level construction projects, two–story building structures, road construction, electrical, plumbing, and masonry projects, and earth moving projects. The unit’s most recent project involved building a firing range at Fort Devens, Massachusetts. The unit provides each Soldier with a standard tool kit.

                The unit is split up between four platoons, each consisting of 30 personnel. The platoons are the First, Second, Third, and Headquarters Platoons. These Platoons are led by a Sergeant First Class, in a leadership position known as a “Platoon Sergeant”, who in turn all answer to a Company First Sergeant.

                The officer leadership of the Platoon consists of “Platoon Leaders”, who are First or Second Lieutenants. They can take leadership of the Platoon when the Platoon Sergeant is not available. They advise the Platoon Sergeants of the Company Commander’s intentions and help identify training and mission objectives. They answer to the Company Commander (OIC), who is at the rank of Captain.

                The Company Commander is the highest-ranking officer within the Company structure, while the highest-ranking Sergeant is the Company First Sergeant (NCOIC).  The NCOIC receives guidance about unit training and mission objectives from the OIC, while at the same time advise the OIC in refining these guidelines. Both the OIC and NCOIC host promotion boards for Soldiers under consideration for promotion.

                The male to female ratio of the company is 5:2; or approximately 40% of the unit consists of females. The positions of the Company enlisted range throughout the spectrum of US Army engineering positions. All Army positions are known as “MOS’s” in Army jargon, followed by an administrative number and letter combination that designates the specific job. For example, the position of electrician in the US Army receives a coding of “MOS 12R”.

                Several enlisted members of the unit have prior MOS’s, which they gained from required job training in other units. Also, many officers and enlisted in the Company have related civilian training and certifications in the construction field. This includes trade licenses, CDL licenses, first aid training, and advanced drafting skills.

                Some enlisted service members do not have any construction related skills. These people are primarily trained as administrative specialists, and are assigned to the Headquarters Platoon. There is a group of mechanics that maintain the Company’s motor vehicle equipment. The medics have received specialized training in responding to field injuries.

                The Headquarters Company is directly under the supervision of the Company Commander, who uses it to facilitate his administrative needs. The Company Commander has asked Headquarters Company to design a database to help track his Soldiers.

                The unit’s leadership wants to know who is current with their regular training requirements, such as PT tests, weapons qualification, command directed safety training briefings, etc. They need the database to identify the unit of assignment for each Soldier and who is in their chain of command.   The unit’s leadership must be able to reach his Soldiers at a residential address and phone.

    The unit’s leadership needs to know if they are current on required medical immunizations. It is also important to know if any Company personnel have any short-term or long-term medical conditions that could affect their abilities to perform duties. All Soldiers height and weight must be tracked and kept within a certain range. Finally, they must know which personnel suffered hot or cold weather related injuries in the past.

                The unit’s leadership wants to identify any enlisted or officer with specialized skill training, either earned in the military or as a civilian, that is related to the Company’s field mission. They are also interested in their current promotion points.  Any issues that can hinder promotion, such as failing PT test, must be noted.

                In light of these needs, both the First Sergeant and Company Commander have asked the unit’s personnel section to construct a relational database that breaks down their administrative needs into four main categories: Administrative Data, Medical Data, Equipment Issued, and Awards and Certifications. They also presented an idea of how they would like to structure their database in a separate power point document.

Mission Statement

The purposes of this database include assist unit leadership with keeping track of its personnel, maintaining address data, unit assignment data, current medical needs, awards and achievements, promotion status, etc.

Mission Objectives

• Maintain (adding, updating, deleting) all residential data on Soldiers.
• Maintain (adding, updating, deleting) all personal data on Soldiers.
• Maintain (adding, updating, deleting) unit assignment data on Soldiers.
• Maintain (adding, updating, deleting) all leadership data on Soldiers.
• Maintain (adding, updating, deleting) all immunization data on Soldiers to see if they need renewal.
• Maintain (adding, updating, deleting) all residential data on Soldiers.
• Maintain (adding, updating, deleting) all basic performance data on Soldiers, to include PT tests, weapons qualification, etc.
• Maintain (adding, updating, deleting) promotion related data on Soldiers.
• To determine if Soldiers are ready for promotion, or if they are flagged for weight, weapons qualifications, PT performance, or UCMJ related reasons
• To search for Soldiers leadership
• To identify Soldiers equipment needs and if they need to pay for missing equipment.
• To identify Soldiers with special medical needs or limitations
• To use this tool to report to higher leadership about unit’s readiness status
• To us e this toll for informing unit’s subordinate leadership what needs to be addressed to maintain unit redness.

 Definitions/Clarifications

Equipment – All tools, equipment, weapons, and gear issued by Delta Company, 302nd Engineering Bn.

Medical – Consists of all preventative medical procedures, such as immunizations, undertaken by military medical authorities for Della Company Soldiers. No immunizations from civilian providers are considered acceptable. However, any medical data related to Permanent Profiles is acceptable if considered legitimate by military medical authorities. “T” denotes a temporary profile lasting no more than six months. “P” denotes a profile denoting a long-term profile lasting more than five years. Alternative PT events, to accommodate the long-term profile, are authorized. If medical conditions are considered sensitive information, the database will state, “see unit medical authorities”. They will only discuss this information if the inquirer needs to know.

Awards – the UCMJ field indicates if the Soldier is undergoing a military related disciplinary or legal process. For privacy sake, the details will not be stated on this database. It will either say “Y” for “yes”, or “N” for “no”. “Clearance” indicates the type of government security clearance the Soldier has.  Most Soldiers will have “Secret”, while others will have “Top Secret”.

Table and Field Structure

1. SoldierPersonalData (PersonellNum, SocNum, LastName, FirstName, Gender, MaritalStatus, StreetAddress, AptNum, City, State, ZipCode, PhoneNum, Children?, DeployedOrders?, FamilyCarePlan?, PersonellNum)

In this table, PersonellNum, LastName, FirstName, are Secondary Keys. The “AptNum” field can be null. Field “FamilyCarePlan?” can only accept “Y” or “N” as an answer. If “DeployedOrders” has data, and “FamilyCarePlan?” is also “N”, then “DeployedOrders” data must be automatically displayed as red. It will only change to black when “FamilyCarePlan” field value is “Y”.

The primary key for this table is SocNum. “PhoneNum” must be data entry enforced to the (nnn)-nnn-nnnn format. “SocNum” is data entry enforced to the nnn-nn-nnnn format throughout this database. With “DeployedOrders?”, the date of the orders must be entered in the nn/nn/nnnn format. Usually deployment orders are issued before Soldiers actually goes overseas.

2. SoldierMedicalData (MedicalFileNum, Height, Weight, BloodPressure, MMR, HIV, Flu, Tentanus, Anthrax, Smallpox, HotWeatherInjury?, ColdWeatherInjury?, Profile?, Vision, Glasses?, Medications?, SocNum)

Throughout this database, most fields that end in a “?”, such as “Glasses?, must be set to only receive input of either “Y” or “N”.  Also, tables in this database use “SocNum” as the foreign key.

“Height” can only receive an input of n-ft, nn-in. “Weight” can only receive input format of nnn-lbs. If a Soldier exceeds weight standards, the input value will be auto-displayed in red. Over weight is determined by height. “BloodPressure” must be put in the nnn/nn format.

The various vaccine fields will be answered with the date that the Soldier last received the shot. If the current time exceeds the date entered by one year, the date will change from black to red. Throughout this database, all data in red either indicates that the Soldier is overdue or has not met a standard.

This only applies to the “Anthrax” and “Smallpox” fields (if a date, using the standard database date format, is already there) when the database sees date data entered in the “DeploymentOrders” field of the SoliderPersonalData Table. “Smallpox” and “Anthrax” can be null values.

These shots are only given when Soldiers are about to deploy. The foreign key for this table, “SocNum” from the SoldierPersonalData Table, references “MedicalFIleNum” in a one to one relationship.  The purpose of SoldierMedicalData is to give the unit leadership an idea if a Soldier is up-to-date with required immunizations.

3. SoldierWeaponData (WeaponNum, QualifiedDate Hits, SocNum)

The foreign key is “SocNum” from the SoldierPersonalData table, referencing primary key “WeaponNum” in a one to many relationship. Though very rare, Soldiers can be issued more than one weapon. Most units either issue M16’s, M79’s (which is an M16 with a grenade launcher mounted on it), M9 pistols, or SAW249’s, which is a light bullet chain-fed machine gun. The numbering scheme for “WeaponNum” must be data input enforced in the following manner:

SAW249 = SAW249nnnnnnn

M79 = M79nnnnnn

M16 = M16nnnnnnn

M9 = M9nnnnnn

This is a made up numbering system, I am not familiar with the serial number schemes used by the Army for weapons besides the M16 or M79. “Hits” field must be data input enforced in the nn/40 format. “QualifiedDate” will be entered in as a date in the nn/nn/nnnn format, turning red when the current time exceeds the date entered by one year.

If the QualifiedDate is red but the “Hits” table is black, this means that the Solider qualified during the last range, but is due to re-qualify. If both field have red data, then the Soldier is overdue for weapon requalification and did not qualify at the last range. Soldiers must get 23 hits or above to qualify.

4. SoldierPTData (PTCardNum, LastAPFT, Gender, Age, PushUps, SitUps, 2MileRun, Combined Score, PermProfile? AlternativeEvent, AlternativeEventTime, SocNum)

In this table, “PTCardNum” is the primary key, and “SocNum”, from

SoliderPersonalData Table, is the foreign key, referencing “PTCardNum” in a one-to-many relationship. Soldiers may have taken so many PT tests in the unit that they have more than one handwritten PT chart

“LastAPFT” must be entered in nn/nn/nnnn date format, and will turn red if the current time exceeds the previously entered date by a year. If a Soldier fails a PT event, with standard scores determined by age and gender, the numbers in either “PushUps”, SitUps, or “2MileRun” are automatically displayed in red.

“PermProfile?” can be a null value field, but it will automatically enter “Y” if the database sees a “P” in the “Profile” field in SoldierMedicalData Table (“T” is Temporary profile, “N” is for None).

“AlternativeEventTime” references the performance time for Soldiers who are authorized to do the 2.5-mile walk as a result of a permanent profile against long-distance running. The time data entry format used for this field is nn:nn.“

“CombinedScore” must be manually entered by the unit Personnel section, as there are different scores granted, depending on age and gender, for the same amount of exercise repetitions done within a set time.

5. SoldierEquipment (SupplyAccountNum, TA50InvDate, TAStmntofCharges, ToolKitInvDate, ToolStmntCharges, SocNum)

In this table, “SocNum” is a foreign key, from SoldierPersonalData Table, referencing primary key “SupplyAccountNum” in a one to one relationship.

 Though this field can be a null value, the commander will know if a new Soldier has not been issued their TA50 gear when a date has been entered in the “TA50InvDate field, using the nn/nn/nnnn format. All Soldiers are given an initial inventory when they have been first issued their field gear (called “TA50”).  

The same conditions and data entry relationships will apply to the ToolKitInvDate” fields. If the current time exceeds the dates entered in the “TA50InvDate” and “ToolKitInvDate by one year, the dates will turn red. The Soldier needs another equipment inventory.

The supply section OIC or NCOIC should be the only ones who can alter “TAStmntChrgs” and “ToolStmntChrgs”, if data entry authority conditions can be set in ACCESS. It uses the “$0000.00” format. The goal is to make the leaders of the supply departments be the final decision makers regarding statement of charges for Soldiers, who will hopefully act in the Soldier’s best interest.

If missing equipment can be replaced by overstock, etc, it should be used to reduce Soldier liabilities, as determined by the supply section OIC/NCOIC. Statements of Charges are usually absorbed by the Soldier’s next paycheck.

6. SoldierCivEmployer (CivPositionFileNum, SocNum, EmployerName, EmployerPhone, Certifications, SpeicalSkills)

In this table, “CivPositionFileNum, ” is a primary key. “SocNum” is again a foreign key referencing the primary key field of the SoldierCivEmployer Table.  Phone numbers in this database are entry enforced to the (nnn)-nnn-nnnn format. “SpecialSkills” field should be wide enough to hold at least two brief sentences.

This is a one to many relationship table, as Soldiers may have more than one civilian job. However, the database is limited to listing two jobs per Soldier.

SoldierPromotions (PromotionSeqNum, SocNum, Rank, DOR, MOS, PromotionPts, Promotable?, PromotionDLComplete?, LastNCOER, PersonellNum)

In this table, PromotionSeqNum is the primary key, which is essentially an orders number granting a Soldier’s promotion. SocNum” is again a foreign key, referencing PromotionSeqNum in a one to many relationship. Soldiers can be promoted multiple times, and each promotion is given a promotion order number.

 DOR means date of rank, which indicates the date that the Soldier’s current rank was granted. It is data entry enforced to the nn/nn/nnnn format. A Macros can be established where the database will force a new DOR date to be entered after a new promotion order number is entered.

MOS is military jargon for “job”, and uses the following coding format: number-number: capital latter. For example:  11B. This field must be data entry enforced to this format.

Promotion points are the number of awarded points a Soldier has earned from various documented acts of service, which is calculated in a process done between the until Personnel Section and the higher leadership in a review board process. “Promotable?” is a data entry enforced “Y” or “N” field.

“Cut off” scores for MOS groups, as determined by higher echelons of the Army, determine PromotionStatus”. If a Soldier has accumulated the required promotion points needed to exceed the MOS group “cut off” scores determined by the Army.

Soldiers are required to perform on-line distance learning before attending a formal promotion school. “PromotionDLComplete?” is data entry enforced “Y” or “N”, and when the database sees “N” in this field, the Promotable? field is automatically set to “N”. Other fields of other tables in the database also control this field.

“LastEval” field indicates if a Solider is due for a performance evaluation. For Sergeants, it is called an NCOER. Officers & Warrant Officer evaluations are called OER’s, and Soldiers below Sergeants are provided with ER’s (Evaluation Reports). In this case, a date entered in this field should turn red within 90 days before its one year anniversary, giving indication that a Soldier will have an evaluation report come soon.

If a Soldier has red data in the “Qualified” field of the SoldierWeaponData Table, or red data in the “LastAPFT”, “Pushups”, “SitUps”, or “2MileRun” fields, the “Promotable?” field is automatically set to “N” .

When a Solider is promoted, LastEval field is auto-populated with the DOR of the Soldier’s new rank. It used the standard date entry format already described in the database. It is also configured to have the data entry go red 90 days prior to the one-year anniversary of this date value. This serves as a warning that the chain of command must complete the Soldier’s evaluation before being considered “late”. 

“Rank” field as data entry enforced in the following format: Capital letter E or O or WO, and n=1-9. Examples are E5, O2, and WO4. “E” is for enlisted ranks, “O” is for officer ranks, and WO is for Warrant Officer Ranks. The highest number for WO is “4”.

7. SoldierClearenceStatus (BadgeNum, ClearenceLevel, Granted, SocNum)

In this table, “BadgeNum” is used for database reference purposes since this unit issues no security badges since it is an engineering unit. ”ClearenceLevel” is data entry enforced to either “Secret”, “Top Secret”, or “Revoked”.

If ClearenceLevel is “Revoked”, the “Promotable” field in the SoldierPromotions Table is automatically set to “N”, if this is possible in ACCESS. In this case, only the security clearance administrator has the power to lift this type of promotion bar. “SocNum” is again a foreign key from the SoldierPersonalData table, referencing the “BadgeNum” field in a one to one relationship.

In the “Granted” field, if the entered time is 4 years after, and “ClearenceLevel” value equals “Top Secret,” the data will become red. The same changes will apply to the “Granted” field after nine years of the entered date if the “Clearance” field value equals “Secret”. This indicates that another clearance re-investigation is due within a year.

Additional Points 

Ideally, each section of the unit should only have the power to view and alter the portion of the database that is relevant to the section accessing the database. For example, the unit supply section is the only section that can alter the SoldierEquipment Table. The unit armorer is the only one who can alter the SoldierWeaponData table. The unit security clearance manager is the only one who can make alterations to SoldierClearenceStatus Table.

If possible in ACCESS, I would restrict all viewing access of the SoldierMedicalData table to the medical section only. All sections can see SoldierPersonalData, but only the unit’s personnel section can add or alter records to this table.

The unit’s Personnel section, the Company First Sergeant, the Company Executive Officer, and the Company Commander are the four entities that can view the entire database, minus SoldierMedicalData Table, but their altering privileges by these four entities should be limited to the SoldierPersonalData, SoldierCivEmployer, SoldierPTData and SoldierPromotions Tables.

For this to happen, ACCESS must be capable of synthesizing access protocols based on granted user accounts.  In this case, the database must be integrated into an information server, with access control accounts established by a server administrator, who sets access policies based on who is using the database and why.  There is little that can be done against falsifying records, so the unit commander must have the authority to request paper documentation, if required, that justifies any data entered into this database.

Addendum Project Notes

1)      With the “SoldierMedicalData” Table, the unit recently had an “SRP”, which is a scheduled event where a medical unit administers shots and takes vitals for all members of a unit at once. This is the reason why immunization information has the same dates. Any deviations from the dates represents newly arrived Soldiers.

2)      Randomly generated blood pressure figures based on this online source:

http://www.bloodpressureuk.org/BloodPressureandyou/Thebasics/Bloodpressurechart

3)      I asked someone for help with this project on 12/2/2015, and the person ended up destroying one of my tables, “SoldierPersonalData”. It had to be reconstructed, so I ended up reducing the data sample of 120 to 50 records for all tables.

4)      In my experience, Anthrax and Smallpox shots are only given to Soldiers about to deploy in an area where they could be exposed to these bio weapons. There, there is no date in these fields (“NA”) except for the two Soldiers who have been tasked with deployment in the “SoldierMedicalData” Table.

5)      In the “SoldierMedicalData” Table, the type of medications are not specified in order to facilitate medical privacy. This information has been reserved for the “paper chart” only. Also, the reasons for the profile have not be specified because of privacy concerns.

6)      The “QualifidedDate” field for SoldierWeaponData Table is the same date since the unit as a group went to a weapons qualification range on 06/30/15. To qualify with a weapon, one needs to hit 23 targets out of 40.

7)      With “SoldierPTData” Table, the combined scores were based on the US Army PT calculator found at the following link: http://apftcalculator.com/

In order to pass the APFT, you must score at least 60% in each PT event. For those on permanent profile, they must get 60% in push-ups and sit-ups and get a passing score in the 2.5 mile power walk. http://army.com/info/apft/alternatetable

8)      With the SoldierEquipData Table, the TA50InvDate and ToolKitInvDate fields have the same dates because there was a companywide Soldier equipment inventory on 12/3/2014.

9)      The equipment inventory due report and the PT_test_due reports are set to alert the command if a Solider has not has not gone through these processes in more than a year.

10)  The medical reports and the NCOERdue reports are set to alert the command if a Soldier has not gone through these processes within nine months, or one year minus 90 days.

11)  At the time of the Soldiers NCOER, all military training and testing is reviewed and converted to promotion points. Promotion is partly based on a review of accumulated promotion points and comparing them to required “thresh hold” points for promotion to the next rank in the Soldier’s MOS. This value is published in the Army Times changes on a monthly basis depending on need for people to be promoted in a certain MOS.

12)  The non-promotable report looks at several variables throughout the table that can flag a Soldier against promotion. This includes PT test failures, not qualifying with weapons, being on a temporary medical profile, pending UCMJ actions, etc.

13)  Reports used the following conditional formatting formulas (done with assistance):

IIf(DateDiff(yyyy, [LastAPFT], Now()) > 1 , true, false)  PT Test Due

More than a year formula

 IIf(DateDiff("d",DateAdd("yyyy",1,[LastNCOER]),Now())<=90,True,False) Last NCOER

“90 day window” formula

14)  The report for adding new promotion records was done with some outside consulting.

15)  Being late with immunization shots will also make a Soldier non-deployable. However, the “NotDeployable” query view is meant for administration use, with all medical information kept out in order to maintain medical privacy. The Personnel section will have to consult with the Medical staff to see if there are any Soldiers on this list when considering who is not deployable.