Dear Sharon:
I presume you will be stocking or manufacturing a product and then
selling or shipping orders. Your products are either serial numbered
or in lots. The above begins to describe just how much variation
there is in performing such functions.
In addition, you may have multiple warehouse locations of the
inventory, and multiple channels through which inventory is added or
removed.
There will probably also be functions to count inventory by lot or
serial number and make adjustments.
I do not know of any free examples of this. It is likely to be a
rather custom job in any case. I've done several such projects, and
no two are alike.
The FIFO or LIFO functions are not really the problem. Once the
actual business processes are understood and modelled, this is
relatively simple compared to the process of designing table, indexes,
and relationships that will make it all work. In fact, the "Table
Design" portion of the project will need to be aware of needs such as
FIFO or LIFO, and the methods by which it will work when it is
implemented later on will be pretty obvious, because you will have
designed the basis for inventory relief into the tables themselves.
So, what I'm suggesting is that you need to concentrate on table
design with this functionality in mind. Hopefully, that's what you
had in mind when you asked this question.
If your design is normalized and contains the data necessary to
perform these functions, then we can continue.
It would be useful for you to know that I recently received a SQL
Server Magazine "Innovator Award" on what I think is exactly the topic
you will need. The generalized technique is what I call "Matching
Consumption." This is a generalized term for a technique of matching
quantities between two lists, such as your inventory "on hand" (or
even scheduled production as well) against orders to be shipped. As
this is yet to be published, I cannot disclose all of it here. But I
could send you a portion of a draft of that article with the
understanding that I hold the copyright to it. Perhaps then we could
discuss how to implement something from this.
If you wish to do this, please respond in this newsgroup and also by
direct email, so I may send you this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
