how to substract sales from stock with oldest date first and then move to next date?

[illusionek]

Hello,



I have attached a sample of a database, which hopefully illustrates my problem well.

Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I
have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is
18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10

For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date
which is 14/10.

I hope all this makes sense and someone would be able to help me out.

I have also attached a copy of Excel spreadsheet, which illustrates what I am trying to achieve. It is updated manually at the moment ;(

[June7]

Bing: Access inventory database first in first out

Here is one discussion

http://www.utteraccess.com/forum/FIF...-t1410760.html

And a link referenced in that thread http://www.allenbrowne.com/AppInventory.html

However, unless you are trying to value the inventory, why does the date matter? Sum product in and sum product out, the difference is balance on hand.

[orange]

I agree with June7. You should research FIFO Inventory Control/Management