Fifo

[Perfac]

A couple of years ago I found this example on the internet. I cannot remember who the author is. The example is great. However, there is one challenge to fix.


I should be able to solve it, but the past couple hours some brain cells are sleeping.
1. I add a simple fourth product, and then a purchase transaction on that 4th product.
2. The last report "Stock Value Report on Fifo basis" should list that newly added product, without doing a sale on that product as well.
3. If I add a quantity of 50 of the new product, and then capture a sale on a later date of 40, the report returns 10 which is correct.
4. If I don't capture a sale, it should return 50 as product available. It does not. That must be wrong.
5. I am sure something small will correct it.

[orange]

You may search for posts by user writeprivate in the forum.
We discussed FIFO earlier this year.

[Perfac]

Thank you. I've studied "WritePrivate's" posts and made the change you suggested to him. When a new product is registered, and a purchase is made on that product, no profit would have been made without selling some of the new product. The new product will not and should not appear on 5 of those reports, (it seems to work correct in that db) the new product should only appear on the last report. I assume the solution for my challenge is for starters that qryTotalSales should list the new product with zero sales, since there are no sales on that product. Currently qryTotalSales does not list products with no sales, and that leads to the error I experience. I will have a cup of coffee, maybe my sleeping brain cells will wake up soon.

[orange]

Not sure I am following: Why would a Product with No sales be in qryTotalSales?

I took my latest database; registered Product Perfac; purchased 4 Perfac @ $ 20/ea.

With QryStockAvailableWithAndWithoutSales, I get the following (I added the colour)

BatchNo	PDate	ProductDesc	Purchased Qty	PPrice	AvailableQty	StockValue
1003	03-Jan-14	APPLE_PFRD	300	14	4	56
3456	19-Jan-23	TEST_OVERSELL	3	17	1	17
10004	20-Feb-08	PRODUCT-A	200	55	100	5500
10005	01-Mar-08	PRODUCT-A	500	60	500	30000
20004	23-Feb-08	PRODUCT-B	250	135	100	13500
20005	11-Mar-08	PRODUCT-B	1000	140	1000	140000
30003	01-Feb-08	PRODUCT-C	1700	260	669	173940
30004	12-Mar-08	PRODUCT-C	2000	270	2000	540000
30005	06-Feb-23	TEST_NOSALES	20	75	10	750
30006	02-May-23	Perfac	4	20	4	80

Here is png of the last report @2-May-23

Attachment 50186

Current Gross Availability



Product/Sales/Purchases

Attachment 50187

[orange]

Perfac er al.

Attached as zip is the latest database I have worked related Fifo Batch Allocation.
Current to 02-May-23 1:15 PM EDT

[Perfac]

Thank you very much. It seems good. I will work with it and reply if any other issue.