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
|
|||
|
|||
Database to track inventory in company cars
I have built a database for my company (a small electrical contracting firm)
using various access 2007 templates. At the moment it contains customer contact details, employee details, a tendering/job pipeline tracking leads and jobs won, a warehouse register tracking our tools in/outs and a vehicle details which tracks the expenses on our company cars. I want to add to the vehicles portion a way to track the materials in each car so that we know when to reorder stock. Each van contains the same stock/amounts but I want to be able to generate orders for each van when stock reaches a min. level for that particular car. I was thinking a table of inventory but im not sure how to allocate it to each car with their own min/max system (maybe a stock location table??). Not sure what transaction tables/queries would be needed to perform this task. Any help would be greatly appreciated |
#2
|
|||
|
|||
Database to track inventory in company cars
To begin, you need a table to identify each material:
TblMaterial MaterialID fields that identify a specific material item Then you need a table to identify each Van: TblVan VanID fields that identify a specific Van such as make, model, year, vehicleID# Since each Van carries the same materials and your post implies each Van has the same reorder point for each item of material, you need a material reorder point table: TblMaterialReorderPoint MaterialReorderPointID MaterialID MaterialReorderPoint Now you need to record the inventory of each item of material in each Van: TblMaterialInVanInventory MaterialInVanInventoryID VanID MaterialID MaterialInVanInventory To generate a form or report that shows what items of material need ordered for eac Van, you eed a query that includes all the above tables. In the query, you identify the item of material from TblMaterial, you identify the Van from TblVan. In the query you need a calculated field, MaterialInVanInventory (TblMaterialInVanInventory) - MaterialReorderPoint (TblMaterialReorderPoint). Set the criteria of the calculated field to 0. In the query you can sort by an appropriate Van field to put the Vans in ascending order and you can sort by an appropriate Maerial field to put the Materials to reorder for each Van in ascending order. Steve "Ginge" wrote in message ... I have built a database for my company (a small electrical contracting firm) using various access 2007 templates. At the moment it contains customer contact details, employee details, a tendering/job pipeline tracking leads and jobs won, a warehouse register tracking our tools in/outs and a vehicle details which tracks the expenses on our company cars. I want to add to the vehicles portion a way to track the materials in each car so that we know when to reorder stock. Each van contains the same stock/amounts but I want to be able to generate orders for each van when stock reaches a min. level for that particular car. I was thinking a table of inventory but im not sure how to allocate it to each car with their own min/max system (maybe a stock location table??). Not sure what transaction tables/queries would be needed to perform this task. Any help would be greatly appreciated |
#3
|
|||
|
|||
Database to track inventory in company cars
Ginge
You might want to read this article on updating quantity on hand by Allen Browne. http://allenbrowne.com/AppInventory.html good luck, you have a big project in front of you. Let me ask a couple of questions. 1. Are there different minimum levels per part per truck or does the same part on every truck have the same minimum level? 2. How are you going to receive the amount used information from the drivers? Each van contains the same stock/amounts but I want to be able to generate orders for each van when stock reaches a min. level for that particular car. I was thinking a table of inventory but im not sure how to allocate it to each car with their own min/max system (maybe a stock location table??). Not sure what transaction tables/queries would be needed to perform this task. Steve’s table design is fine. I do disagree on one point. If you have the same reorder point for all parts on all cars, I would put the reorder point on the TblMaterial table. The TblMaterial table should have the following data: Key – Part Number Data – Part Description Reorder Point TblVanInventory table: 2 part key Key - Vehicle Number Part Number Reorder point (if the reorder point is by part on each car) TblVanInvTrans table: Key - Automatic assigned sequent number Data Transaction (Sold, On Order, Re-Stock) Data Vehicle Number Part Number Transaction Date Quantity Sold Quantity Ordered Quantity Re-stocked Comment When you write a sold transaction, the amount used / sold would be in the Quantity Sold field. When you write an order transaction, the amount ordered will be placed as a positive number in the Quantity Ordered field. When you write a re-stock transaction, the amount re-stocked will be placed as a negative number in the Quantity Ordered field and as a positive number in the Quantity Re-stocked field. The advantage of this is you can run a query and total up the Quantity Sold, Quantity On Order, and Quantity Re-stocked. The total quantity on order should be equal to the amount that is currently on order for this part. I’ve read Allen’s article and he has a point about storing the amount on hand, amount sold, and amount on order. It is very complicated to make sure you’ve covered all of the bases. The advantage of using Allen’s approach is it makes development very easy. However, if you have a large number of transactions and you are accessing the data over the network, there is an time impact to re-compute the quantities on hand, quantities on order, and quantities sold. At what point that time impact become a problem, I don’t know that is something that you would have to ask the MVPs. Good luck, Dennis |
#4
|
|||
|
|||
Database to track inventory in company cars
Dennis,
The same part in each van has the same minimum level I just need to be able to track the items on a van by van basis so I can do orders for each van individually. The information is received by a stock card system. When items are used they are marked on a card for that job and handed into the office. I want to be able to enter this information almost like a transaction/sale so the amount comes off the van and I can then generate a report/order. I then also want to enter the incoming stock we receive from the suppliers back into the van. (hopefully not one item at a time!) Thank you for the link I will try to incorperate it into my design! "Dennis" wrote: Ginge You might want to read this article on updating quantity on hand by Allen Browne. http://allenbrowne.com/AppInventory.html good luck, you have a big project in front of you. Let me ask a couple of questions. 1. Are there different minimum levels per part per truck or does the same part on every truck have the same minimum level? 2. How are you going to receive the amount used information from the drivers? Each van contains the same stock/amounts but I want to be able to generate orders for each van when stock reaches a min. level for that particular car. I was thinking a table of inventory but im not sure how to allocate it to each car with their own min/max system (maybe a stock location table??). Not sure what transaction tables/queries would be needed to perform this task. Steve’s table design is fine. I do disagree on one point. If you have the same reorder point for all parts on all cars, I would put the reorder point on the TblMaterial table. The TblMaterial table should have the following data: Key – Part Number Data – Part Description Reorder Point TblVanInventory table: 2 part key Key - Vehicle Number Part Number Reorder point (if the reorder point is by part on each car) TblVanInvTrans table: Key - Automatic assigned sequent number Data Transaction (Sold, On Order, Re-Stock) Data Vehicle Number Part Number Transaction Date Quantity Sold Quantity Ordered Quantity Re-stocked Comment When you write a sold transaction, the amount used / sold would be in the Quantity Sold field. When you write an order transaction, the amount ordered will be placed as a positive number in the Quantity Ordered field. When you write a re-stock transaction, the amount re-stocked will be placed as a negative number in the Quantity Ordered field and as a positive number in the Quantity Re-stocked field. The advantage of this is you can run a query and total up the Quantity Sold, Quantity On Order, and Quantity Re-stocked. The total quantity on order should be equal to the amount that is currently on order for this part. I’ve read Allen’s article and he has a point about storing the amount on hand, amount sold, and amount on order. It is very complicated to make sure you’ve covered all of the bases. The advantage of using Allen’s approach is it makes development very easy. However, if you have a large number of transactions and you are accessing the data over the network, there is an time impact to re-compute the quantities on hand, quantities on order, and quantities sold. At what point that time impact become a problem, I don’t know that is something that you would have to ask the MVPs. Good luck, Dennis |
Thread Tools | |
Display Modes | |
|
|