Fifo Bases Sales in MS Access (1 Viewer)

[bkhawaja]

Please help to to get out of situation

I have Product A with 3 different batch numbers

Product Batch Qty
A 1 10
A 2 10
A 3 10

If I put sell value 25 items then automatically sales report should be seen like this

Product Batch Sale
A 1 10
A 2 10
A 3 5

TIA.

 

[CJ_London]

not much to go on - suggest you need a running sum of your batches, find the one that has the same or more than sold, then for each batch included in that running sum record, display the lower of the max of running sum for that batch or the quantity.

a running sum in a query might be something like

runningSum:dsum("qty","myTable","Product='" & [Product] & "' AND Batch<=" & [Batch])

with a criteria <=25

If you need a more detailed response, you will need to provide a lot more background to your table design, sample data, relationships, etc

 

[jdraw]

You may get some FIFO ideas from this thread.
And/or this link or this.
Also check youtube fifo for some free videos.

 

[MajP]

As mentioned you need to provide real tables and real data. Doing this should be relatively easy, but my guess there is more to it. Like how do you deprecate the amounts after filling the order?

 

[Pat Hartman]

When the user enters "sell 25", the calculation should take place at that time to sell 10, 10, and 5 for the relevant batches. The report should just be showing what is in the table.

 

[gemma-the-husky]

@bkhawaja Is this just an attempt to manage your stock quantities, and you aren't REALLY trying to use stock from those particular batches? If so it's rather complicated and unnecessary.

eg -
Are the orders really using stock from the FIFO batches, or is this just a database treatment. I assume the latter, If it's really the former, then you wouldn't want to attribute products from the wrong input batch to sales or production orders, so it's not as simple as picking the stock in FIFO order.

Also, what if you have multiple orders going through at the same time under your plan. You won't be able to safely enter Order 2 until Order 1 has completed in case both Order 1 and Order 2 try to use the same stock item. At any rate, you would need to consider some process to deal with this within the stock issue management code. Could you get into a deadlock situation with competing orders? Could you find you have insufficient stock to fulfil an order? how do you resolve these last two cases.?

 

[arnelgp]

@bkhawaja Is this just an attempt to manage your stock quantities, and you aren't REALLY trying to use stock from those particular batches? If so it's rather complicated and unnecessary.

that was 3 months ago.

 

[gemma-the-husky]

@arnelgp

Strange. this was showing as a current thread when I looked. I wasn't trawling through old threads. Out of interest @bhelmy has just raised a very similar question though. I thought it was the same thread to be honest.

(1) FIFO | Access World Forums (access-programmers.co.uk)