Maintaining a history of item movement within an inventory databas

[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.

 

[Beetle]

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.

 

[Steve]

I suggest you need a different table structure ...........
TblStatus
StatusID
Status

TblAssett
AssetID
Serial Number
Description
Manufacture
Cost
StatusID

TblEmployee
EmployeeID
Last name
First name
Title

TblBuilding
BuildingID
BuildingName

TblBuildingRoom
BuildingRoomID
BuildingID
BuildingRoomNumber

TblEmployeeRoom
EmployeeRoomID
EmployeeID
BuildingRoomID
RoomAssignmentStartDate
RoomAssignmentEndDate

TblAssetRoom
AssetRoomID
AssetID
BuildingRoomID
AssetInRoomStartDate
AssetInRoomEndDate

You can get the history of any asset's movement from TblAssetRoom and if you
want to tie this into employees, include TblEmployeeRoom.

Steve
(e-mail address removed)




"R. Blankenship (Deming Public Schools)" <R. Blankenship (Deming Public
Schools)@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[R. Blankenship (Deming Public Schools)]

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.

 

[Beetle]

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.

 

[Fred]

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.

 

[R. Blankenship (Deming Public Schools)]

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]

1. Create a query of TblBuildingRoom and sort ascending on
BuildingRoomNumber.
2. Create a form named SFrmBuildingRoom based on the query in 1.
3. Create a query that includes TblEmployee and TblEmployeeRoom.
4. Create a form named SFrmEmployeeRoom based on the query in 3. Make
this form a continuous form.
5. Create a query that includes TblAsset and TblAssetRoom.
6. Create a form named SFrmAssetRoom based on the query in 5. Make this
form a continuous form.
7. Go to the database window. Click on Window - Tile Vertically.
8. Click and drag SFrmEmployeeRoom and drop it on SFrmBuildingRoom to
make it a subform on SFrmBuildingRoom.
9. Click and drag SFrmAssetRoom and drop it on SFrmBuildingRoom to make
it a subform on SFrmBuildingRoom.
10. Create a query of TblBuilding and sort ascending on BuildingName.
11. Create a form named FrmBuilding based on the query in 10.
12. Go to the database window. Click on Window - Tile Vertically.
13. Click and drag SFrmBuildingRoom and drop it on FrmBuilding to make it
a subform on FrmBuilding.

You will now have a form, FrmBuilding, containing two subforms where you can
enter both an employee assigned to a selected room and an asset assigned to
the same room.

You can get the history of any asset's movement from TblAssetRoom and if you
want to tie this into employees, include TblEmployeeRoom.

Steve
(e-mail address removed)





"R. Blankenship (Deming Public Schools)"

 

[Fred]

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