Not lookup, but assignment

[ArmyLT]

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

[hertfordkc]

No answers, just questions:
1) might you have naming ambiguity, e. g. the same field name in two different tables?
2) Can you have a serial number and not have a weapon/optic... assigned? or no serial number, but have a weapon assigned?

[ArmyLT]

hertfordkc,

I've corrected my original post - it lost all of my carefully placed formatting.

I've got a Soldier Table - with name, roster number, blood type, etc - as well as RifleID, PistolID, OpticID, NVGID - in order to link the the secondary "equipment" tables. In the Soldier Table, RifleID links (in a one to one relationship) with the RifleID (PK) in the Rifle Table. (As shown below)

Soldier Table
[SoldierID] [Name] [RosterNumber] [BloodType] [RifleID]
1 Smith S1234 O+ 3
2 Jones J3839 O- 9

Rifle Table
[RifleID] [SerialNumb] [Type] [IsAssigned]
1 280238 M-4 N
2 208382 M-16 N
3 203023 M-4 Y

(Again, can't seem to get the format right...)

There is a similar setup for each type of sensitive equipment that we need to track outside the wire - optics, pistols, NVGs, etc.

So to directly answer your questions:

1) Yes, I do have the same names in two different tables. Soldier Table<RifleID> and Rifle Table<RifleID>. Are you saying it's best to have something a bit different? Soldier Table<Rifle> links to Rifle Table<RifleID>? More than happy to try it!

2) No. If a rifle, pistol, etc - is assigned to a Soldier, we have to track it by Serial Number.

Again, my lost formatting probably prompted some confusion.

Thanks!

[RayMilhon]

You can have the same field name in multiple tables done this many times with Primary Key to foreign key links between tables.

I am presuming you have a Form where the data is input. I would modify your design slightly

Soldier Table
soldierID
Name
Rank
Rosterbr
Blood Type

then I would add a table called assignments

SolderID
WeaponID
OpticID
NVGID
Date

Your Form Would look at the Assignments table and include a combo box for each of the lookup tables Soldier,Weapon,Optics,NVG. There would also be a Date combo Box at the top so you can specify a specific Date and Filter the form by that date.