Solved How to represent the process of inventory

[cheberdy]

I have a table goods movement. This has the attributes item number, storage bin, quantity change and type of quantity change, which are 1(putaway),2(stock removal) and 3(inventory).To calculate the total quantity of an item I make a query for the table, which adds up the quantity change. However, there is still the process of inventory that applies to one item at a time with a bin. So if one takes the stock of a storage bin physically, the stock of the article in a storage bin should be adjusted in the database. To do this, the difference between the actual stock and the target stock is simply formed. This is then included in the goods movement table. This seems to me however rather complicated in the conversion, because always the stock quantity of an article for a storage bin must be computed, in order to form then the difference and this must take place at the correct place. By this I mean that the inventory takes place on a certain date and thus only then the stock should be adjusted. Would someone perhaps have an idea for the implementation?

 

[plog]

Examples provide the best way to communicate data issues.

I sort of understood the one table you described, but you lost me in what you are trying to implement. Perhaps you can provide some sample data and walk me through a specific demonstration. Or better yet if you ultimately want just a query show me what data that query should show based on a set of starting data.

 

[Pat Hartman]

Although it is technically possible to directly update your inventory by adding and subtracting so that the qty is always the qty on hand, it is impossible to audit and you will never figure out where your computation errors are if you use this method. Therefore the prefered method is to use a transaction table. Every type of adjustment is a separate transaction. When you add inventory, when you subtract inventory, when you move inventory, when you adjust inventory due to a cycle count, etc.

Because inventory comes from some place and goes someplace, you should keep from and to fields in addition to the tran type and qty. That way you can see that -10 of itemA move from bin1 to bin2. You also have a second transaction because this is an internal action and that shows +10 of ItemA moving to bin2 from bin1. You can even connect the transactions with your Purchase orders and with your Sales orders.

 

[Uncle Gizmo]

You may find Allen Browne's article on inventory useful:-

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

 

[CJ_London]

I think the op is saying they have a table of transactions and the question is around stock adjustments- how to calculate what the adjustment should be.

would help to see some example data and the outcome required from that example data

 

[Pat Hartman]

An adjustment is a transaction type. You just sum the transactions to get the quantity on hand. Moving inventory between bins requires two transactions because you add inventory to one but subtract it from another.