Database to track inventory in company cars

  • Thread starter Thread starter Ginge
  • Start date Start date
G

Ginge

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
 
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
(e-mail address removed)
 
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
 
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!
 
Back
Top