View Single Post
  #3  
Old March 26th, 2010, 06:39 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default 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