I am surprised to learn that my questions about inventory management turned up to be such a challenge for You, coz I thought those were pretty straightforward and common. I will try to approach this from a different angle and may be we will be able to work sth out together.
The problem I am dealing with is really simple and had it been a small data sample I would have already dived in and coded the logic. But since there are tens of thousands records I have to make a strategic decision and thought that this forum was the place to go to for advise.
All I need to do is to allocate inventory units to sold batches on FIFO basis. I have provided the samples in my questions. Now, if you can't come up with the total solution, maybe you can point the direction, dos and donts, so that I don't go in circles.
Let me ask you this. Let's compare three data manipulation means : Excel spreadsheet, VB array, Access table. Say, we have this data set
Transaction Units Purchase price Selling Price
Buy 3 100 -
Buy 5 200 -
Sell 4 ? 300
Sell 4 ? 500
Which of the three means would you use to solve for the unknown purchase prices?