If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Maintaining a history of item movement within an inventory databas
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. |
#2
|
|||
|
|||
Maintaining a history of item movement within an inventory databas
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. -- _________ Sean Bailey "R. Blankenship (Deming Public Schools)" wrote: 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. |
#4
|
|||
|
|||
Maintaining a history of item movement within an inventory dat
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" wrote: 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. -- _________ Sean Bailey "R. Blankenship (Deming Public Schools)" wrote: 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. |
#5
|
|||
|
|||
Maintaining a history of item movement within an inventory dat
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. -- _________ Sean Bailey "R. Blankenship (Deming Public Schools)" wrote: 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" wrote: 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. -- _________ Sean Bailey "R. Blankenship (Deming Public Schools)" wrote: 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. |
#6
|
|||
|
|||
Maintaining a history of item movement within an inventory databas
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)" wrote: 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. |
#7
|
|||
|
|||
Maintaining a history of item movement within an inventory dat
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. "Fred" wrote: 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)" wrote: 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. |
#8
|
|||
|
|||
Maintaining a history of item movement within an inventory dat
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 "R. Blankenship (Deming Public Schools)" soft.com wrote in message ... 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. "Fred" wrote: 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)" wrote: 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. |
#9
|
|||
|
|||
Maintaining a history of item movement within an inventory dat
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 "Steve" wrote: 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 "R. Blankenship (Deming Public Schools)" soft.com wrote in message ... 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. "Fred" wrote: 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)" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|