First In, First Out subtraction from rows?

[dee-u]

Hard to suggest anything without more information.

Why are you subtracting from "a" until it's "0", then start subtracting from "b", and so on...

What is the purpose of this process?

It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.

[wes4dbt]

It's a FIFO as I've already mentioned, "a" is the first so it has to be "zeroed" first before moving to the next "stock" which is "b", and so on.

I know, you said that in your first post. You haven't added any clarification.

What does each row represent? Is this an Inventory system or Stocks or ...... If this is an Inventory system then I'd say this is a bad design.

It should have at least 2 Tables, 3 Tables would work well too. A simple example,

2 Table design:

Table 1: InventoryItems - Fields: ItemId ItemDescription Price Cost QuantityOnHand QuantityOnOrder

Table 2: ItemTransactions - Fields: TransactionId ItemId Quantity Cost Total TransactionType (this field would indicate if this was a Purchase or a Sale)

The 3 table method would breakup the ItemTransactions into ItemsPurchased and ItemsSold.

It really depends on how much detailed history you want. If you don't care about Purchase or Sales history and the only thing you will ever want to know is the Average cost then you could use one table.

Table 1: ItemTransactions - Fields: ItemId Quantity Cost Total QuantityBalance

With this method QuantityBalance would initially be the same as Quantity and then you would subtract from QuantityBalance as items are sold.