Personnel andEquipment Tracking System (1 Viewer)

I don’t know if this is the correct place to post this. I’m creating a personnel and equipment tracking system. I will have a table for the personnel and their attributes, a table for desks and their attributes, and a table for equipment and their attributes. There can be more than one person assigned to a desk (a many to one relationship between tblPersonnel and tblDesks) and there can be more than one piece of equipment assigned to a person (a many to one relationship between tblEquipment and tblPersonnel). I think that I may need another table (tblAssignment) that holds the primary keys and foreign keys for the three tables so that I can assign and reassign equipment and desks. I’m a bit confused about which should be the primary and which should be the foreign keys; I’m thinking the table should have the primary key of tblPersonnel and foreign keys of tblDesks and tblEquipment. Does that sound correct? I will be using a Visio map to show the location of the desks and the labels will be the fields of the Last name of the person assigned to the desk. The labels will automatically update when the desks are reassigned.

I need to make a form where I can assign and reassign equipment and desks. I was thinking of using a combo box for the desk numbers and maybe a textbox for equipment serial numbers. I want to error check each change because I don’t want to assign a desk that is already occupied and I don’t want to assign equipment that someone else already has. Could I use a query to check for an equipment assignment if a default value can be set to “vacant” or “unassigned”? I have searched forums for ideas but perhaps I’m missing something. Any suggestions?

Do you need assignment history?

If not, can save DeskID into tblPersonnel and PersonnelID into tblEquipment.

If you need history, then might look at MS Lending Library or MS Assets database templates.

Might need two tables to track assignments.

You may find this free data model from Barry Williams' site helpful.
Inventory of IT Assets

Thank you for your suggestions. JDraw, I looked at the Barry Williams site and it made things more clear. I know how to proceed from here.

Happy to help.
Good luck.