We are using Excel to create "Trip Tickets" for mounted missions that go outside the wire in order to quickly identify impacted personnel and equipment in case of a significant event.
These trip tickets change daily (for each mission), are hard to maintain, and hard to keep accurate due to typos, cut and paste, and copying bad information (typical Excel as a database issues...)
Bumper #1335
Driver: Smith, O+, M4 #8239, NVG #2389
TC: Jones, AB-, M4 #8239, NVG #8290
Gunner: Hays, B+, M16 #8989, NVG #0838
Multiplied by 8 or 10 vehicles
Initially, I'd like to assign weapons, NVGs, and other equipment to Soldiers. A simple screen with name, rank, battle roster, etc. with drop downs that provide a list of available weapons, nvgs, optics, etc.
I was plugging away ok with a Soldier table with SoldierID, name, (etc), WeaponID, NVGID, OpticID. That was then linked to a Weapon table with WeaponID, weapon type, weapon SN via a one to one relationship (One Soldier can only have one Weapon). However, when I added the NVGs to the query - the query became non-updateable. I'm continuing to do research into this - and have found a great deal - but is this design bad? Seems to be fairly well normalized.
Soldier Table
SoldierID
Name Weapon Table
WeaponID <-one to one -> WeaponID from Weapon Table
OpticID <-one to one -> OpticID from Optic Table
NVGID <- one to one -> NVGID from NVG Table
The concept I'm shooting for is I can assign weapons and equipment to Soldiers, and then assign Soldiers to vehicles - in order to print out a trip ticket that shows who is where and what they're carrying...
Is there a better way to design this? It's not really a look-up table, or a typical many to one scenario...
Thanks!
ArmyLT