Dear Cyp:
I suggest it would be much better if you didn't do this. Rather,
whenever it is necessary to know what quantity you have, especially as
of some date, you simply add up all the transactions to get this using
an aggreagate sum of the Quantity.
However, there is a problem with doing this, and some solutions to
that problem.
The problem is that, after a period of time (perhaps a year or two)
the number of rows in the transaction table will become large, and it
will start to get slow.
The first solution is to put another column in the transaction table.
I call this TransactionType. It has different possible values:
purchase, shipped, adjustment, balance forward, and counted.
Counted is the key here. The purchases and adjustments add to the
quantity you have, and shipped subtracts. But counted means that
someone "took inventory" on that date, and that the value therefore
replaces what quantity you have. That way, you need only add up those
transactions for each product that have occurred since the last time
it was counted, plus the quantity found when it was counted. Assuming
each product is counted at least annually, the number of rows to be
considered in summing the quantity do not increase indefinitely.
I know this doesn't sound so simple, but your approach is fraught with
serious problems. How will you track which transactions have been
added into the quantity, and which have not? What if the transactions
are not entered and processed in date order? What if a transaction is
entered with the wrong quantity or the wrong product, is processed
into the UnitsInStock, then the transaction is corrected. How will
you deduct the change from the incorrect product and apply the
quantity to the corrected product? Without the ability to edit any
part of any transaction and still have the correct result, you will
have a nightmare!
So, the approach should be to dynamically calculate the UnitsInStock
from the transactions. This gives you the option to find out how many
were in stock as of last Tuesday, for example. You may also have
purchases that have not yet arrived, but for which you have expected
delivery. When desired, you could add these in and obtain the
expected stock as of some future date, even deducting proposed
shipments of consumption of the product.
Can you see why this would be a better way to handle inventory/
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
