One option I have used was to have a "start of month" stock level.
months movements.
the current stock levels.
a certain date) to an archive table.
I used to have a record in a table for each product for each end of month.
This had fields like:
qty in for month,
qty out for month,
value in for month,
value out for month,
stock at end of month,
and month and year number!
Ray Smith.
...
> Greetings,
> I have a quick question regarding what is the best method for
> tracking
> transactions and balances in an
inventory system. I have been building
> systems for many years and have built
inventory systems. I have built
> them a variety of ways - depending on the type and size of the system.
> But one thing that has bothered me over the years is what is the best
> method for tracking transactions and balances, as it relates to the data
> model. I will make this as simple as possible. Most
inventory systems
> have an item table, or tables, which describes the items in the
>
inventory. Now off of the item table you will have one or more
> transaction tables. One method is to basically track all the positives
> and negatives and then add them up when you create a report. But over a
> period of time, these tables could get pretty large, thus, reporting
> slows down. One method is to track the balance in each record, but this
> means you must have a calculated field in your table. Some schools of
> thought says putting calculated fields in a table is a taboo. Another
> method is to have a table that tracks all of your increases in your
>
inventory and then a table that tracks your decreases in
inventory, then
> does the calculating off these two tables. And I have seen several
> other variations of these "simplistic" examples I described. I have
> used variations of these methods based on the type and size of the
> system. Now my question: What is the "best" method? Please let me
> know from your experiences of what has worked the best for you.
> Thank you.
> --
> Gary A. Harris, CSP, BS
> PROptions
> Post Office Box 5879
> Olympia, Washington 98509-5879
> Internet:
http://www.proptions.com > Antiques/Collectables:
http://www.proptions.com/antiques.htm > E-Mail: mailto://i
...@proptions.com
> or mailto://Ga
...@proptions.com
> Phone: (360) 438-3705 (Voice and Fax)
> (360) 923-6713
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
|
