Maintaining a history of item movement within an inventory databas

  • Thread starter Thread starter R. Blankenship (Deming Public Schools)
  • Start date Start date
R

R. Blankenship (Deming Public Schools)

I have an inventory database to track technology assests in our school
district. There are three main tables that deal with this process.

Inventory Item:
Serial Number
Description
Room#
Manufacture
Cost
Status
Status Date
(etc.)

Employee:
Last name
First name
Room#
Title

Rooms:
Room#
Purpose
Location (building)

I tie the three tables together via room number.

Our employees are constantly moving from room to room, building to building,
sometimes taking equipment, sometimes not. I need a way to see the history
of movement of any given piece of equipment - Right now I use the status (in
use, retired, etc.) and status date to see what the last placement was, but I
would prefer to be able to lookup an item and see where it has been over the
last couple of years.
 
You have a many-to-many relationship, so you need a junction table to
define it. An example table structure;

Note: PK = Primary Key, FK = Foreign Key

tblInventoryItems
*************
ItemID (PK)
Serial Number
Description
ManufactureID (FK to tblManufacture)
Cost
Status
Status Date
(etc.)

tblEmployees
**********
EmployeeID (PK)
Last name
First name
Title

tblRooms
*******
RoomID (PK)
Purpose
BuildingID (FK to tblBuildings)

tblManufacture
***********
ManufactureID (PK)
CompanyName
Address
etc.

tblBuildings
********
BuildingID (PK)
BuildingName

tblEquipmentUse (the junction table)
************
EquipmentUseID (optional PK. You could also use the 4 other fields as
combined PK)
EmployeeID (FK to tblEmployees)
ItemID (FK to tblInventoryItems)
RoomID (FK to tblRooms)
UseDate

The data stored in the junction table (tblEquipmentUse) would tell you that
Employee X was using Equipment X in Room X on X Date. I added the other
tables (tblManufacture and tblBuildings) as a suggestion to keep you from
having
to repeatedly enter the Manufacture and Building info.
 
Thanks!

Looks like I have two good suggestions - that are pretty similar. So, would
I have to remember to go to the junction table to enter a new record before
transferring a piece of equipment, or is there a way I can automate this? I
would like the table to basically populate itself whenever a change in room #
is made.
 
In this type of situation you woul typically have a Main form/ sub form
where the sub form uses the junction table as it's recordsource. For
example, you might have a Main form based on tblInventoryItems
(or a query thereof) with a subform based on tblEquipmentUse (the
junction table). In the subform you would use combo boxes for selecting
the appropriate data for the first three fields (the FK fields) and a text
box for entering the appropriate date.
 
For better or worse, I'm going to suggest starting by clarifying a few basic
questions even if only to yourself. .

- What will your basic data entries be:

1. LOCATIONS of equipment?
2. MOVEMENTS of equipment?
3. SOmething else (e.g. people related.

Are you trying to infer one form the other (e.g. enter #1m infer #2, enter
#2, infor #1)

If it's #1, what do people have to do with it? (e.g. how it got there is
not "location" Are you also recording the locations of people?

Your table structure should follow your answer to that question. If you
are recording movements, then you should have a movements table etc.
 
Hi Fred...good questions, I will answer them in print, this will help me work
through them in my head. :0)

Currently, I provide each employee with a list of equipment assigned to them
twice per year so they can verify that our information is correct. In
between these two instances, equipment gets shuffled around quite a bit.
When I change the location of an item, I lose the ability to lookup any
previous assignment. Also, when I move an employee from one location to
another - the equipment assigned to them can easily get lost in the shuffle
if the data entry person does not remember to first check that employees'
list as well as verifing the new locations' assigned equipment before making
the actual change. So we sometimes end up with equipmet scattered in the
database at the beignning of the year when 1/2 the employees are suddenly in
a new location and the employee data is changed without first checking the
equipment data.

I guess the answer is, I primarily want to know WHERE equipment is, but
would like to be able to see where is has BEEN. The basic data entry being
LOCATION.
 
Steve has you at the finish line. If that does it for you, then you can skip
the additional questions that I would have.

Sincerley,

Fred
 
Back
Top