FIFO - Demo attached works to consume stock correctly, but how to calculate profit

[ArviLaanemets]

Hi there Avril - thanks so much for replying and taking the time to help me.

1. Just so I understand, are your suggestions relating to the first database that I submitted OR the one Orange has added?

To post #1.

2. Would it be possible (I know this is a big ask) for you to download that sample db, make the changes and repost here. It would be amazing to have two working options and then I can have reports and form view

Sorry, but I don't have Access available currently.

[writeprivate]

@Orange

Hi Jack

I have fully integrated with my existing system and all works well except for one thing.....

The reports only show an output where there has been a sale record as well as a purchase record. This makes sense for the first 5 reports as they are calculating profit etc. But the last report (which is really vital) should show the quantity in stock even if there has been no sale

So in short, the report "Stock Value Report on Fifo Basis" only shows results for those stock items for which there has been a purchase AND and sale - therefore, if I have purchased 10 Microsoft shares but not sold any, then the report is blank and will not show the units available

This "Stock Value Report on Fifo Basis" report is based on the QryStockAvailable query which in turn is based on the QryStockAvailableSub query (see screenshot below)

I have tried to amend the expression to take account of an isnull situation for TSale (where the yellow highlight is) but I think this isn't working because the recordset is already Null as it is based on QryTotalSales which is at the root of the issue. I have also tried to update the QryTotalSales query but cannot work out what to do. As a last ditch attempt, I tried to amend the join properties of the QryStockAvailable query to show all the QryStockAvailableSub and only the QryTotalSales records which match but it is causing an 'Ambigiuous Join" error.

Please could you help? I cannot get this to work

Thanks

UPDATE: Please note you may notice that i have changed some fieldnames from the version you have - but this does not affect the error - I have checked the same behaviour occurs with the orginal version - you just need to delete sales for a particular product and the report will then not show any stock available

[writeprivate]

Hi Jack - as I'm new to the board, I thik I made a mistake posting to the end of the thread rather than directly to your last comment. Please could you take a look at the last post on page 2. I have a groud to a halt error and could really appreciate your insights. Thanks

[orange]

I think I have a workaround, but you'll have to do some checking/verifying with your data.

Here is some sql that should identify QtyAvailable for each Product whether or not they have had sales.
In fact it deals with Sales, and then adds in those that had NO sales to identify total QtyAvailable and then TotalValue of Available Stocks.

The first part is what currently exists. After UNION,is the part that deals with Products that have had no sales.

SELECT QryStockAvailableSub.BatchNo, QryStockAvailableSub.PDate, Products.ProductDesc,
QryStockAvailableSub.PQty AS [Purchased Qty], QryStockAvailableSub.PPrice
, IIf([TSale]>[popn],[pruntot]-[TSale],[pqty]) AS AvailableQty
, [AvailableQty]*[PPrice] AS StockValue
FROM Products INNER JOIN (QryTotalSales INNER JOIN QryStockAvailableSub ON
 QryTotalSales.Product = QryStockAvailableSub.Product) ON 
Products.ProductId = QryTotalSales.Product
WHERE (((QryStockAvailableSub.PDate)<=[forms]![frmreports]![TDate]) AND 
((QryStockAvailableSub.PRunTot)>[TSale]))

UNION 

SELECT Purchase.BatchNo, Purchase.PDate, Products.ProductDesc, Purchase.PQty AS PurchasedQty
, Purchase.PPrice, Purchase.PRunTot AS AvailableQty
, ([AvailableQty]*[PPrice]) AS StockValue
FROM Products INNER JOIN (Purchase LEFT JOIN Sales ON
 Purchase.Product = Sales.Product) ON
 Products.ProductId = Purchase.Product
WHERE  (Sales.Product Is Null AND 
Purchase.PDate<=[forms]![frmreports]![TDate]);

I named this query: QryStockAvailableWithAndWithoutSales

You have to change the record source of the Report: StockReportOnFifoBasis

From: QryStockAvailable

TO : QryStockAvailableWithAndWithoutSales

If you are unclear with this process, let me know, and I'll send a new database with the changes.

It is important to test with some sample data before making this a permanent change.

[orange]

I think I have a workaround/fix, but you'll have to do some checking/verifying with your data.

Here is some sql that should identify QtyAvailable for each Product whether or not they have had sales.
In fact it deals with Sales, and then adds in those that had NO sales to identify total QtyAvailable and then TotalValue of Available Stocks.

The first part is what currently exists. After UNION,is the part that deals with Products that have had no sales.

SELECT QryStockAvailableSub.BatchNo, QryStockAvailableSub.PDate, Products.ProductDesc, QryStockAvailableSub.PQty AS [Purchased Qty], QryStockAvailableSub.PPrice, IIf([TSale]>[popn],[pruntot]-[TSale],[pqty]) AS AvailableQty, [AvailableQty]*[PPrice] AS StockValue
FROM Products INNER JOIN (QryTotalSales INNER JOIN QryStockAvailableSub ON QryTotalSales.Product = QryStockAvailableSub.Product) ON Products.ProductId = QryTotalSales.Product
WHERE (((QryStockAvailableSub.PDate)<=[forms]![frmreports]![TDate]) AND ((QryStockAvailableSub.PRunTot)>[TSale]))
UNION SELECT Purchase.BatchNo, Purchase.PDate, Products.ProductDesc, Purchase.PQty AS PurchasedQty, Purchase.PPrice, Purchase.PRunTot AS AvailableQty, ([AvailableQty]*[PPrice]) AS StockValue
FROM Products INNER JOIN (Purchase LEFT JOIN Sales ON Purchase.Product = Sales.Product) ON Products.ProductId = Purchase.Product
WHERE  (Sales.Product Is Null AND Purchase.PDate<=[forms]![frmreports]![TDate]);

I named this query: QryStockAvailableWithAndWithoutSales

You have to change the record source of the Report: StockReportOnFifoBasis

From: QryStockAvailable

TO : QryStockAvailableWithAndWithoutSales

If you are unclear with this process, let me know, and I'll send a new database with the changes.

It is important to test with some sample data before making this a permanent change.

[writeprivate]

I think I have a workaround/fix, but you'll have to do some checking/verifying with your data.

Here is some sql that should identify QtyAvailable for each Product whether or not they have had sales.
In fact it deals with Sales, and then adds in those that had NO sales to identify total QtyAvailable and then TotalValue of Available Stocks.

The first part is what currently exists. After UNION,is the part that deals with Products that have had no sales.

SELECT QryStockAvailableSub.BatchNo, QryStockAvailableSub.PDate, Products.ProductDesc, QryStockAvailableSub.PQty AS [Purchased Qty], QryStockAvailableSub.PPrice, IIf([TSale]>[popn],[pruntot]-[TSale],[pqty]) AS AvailableQty, [AvailableQty]*[PPrice] AS StockValue
FROM Products INNER JOIN (QryTotalSales INNER JOIN QryStockAvailableSub ON QryTotalSales.Product = QryStockAvailableSub.Product) ON Products.ProductId = QryTotalSales.Product
WHERE (((QryStockAvailableSub.PDate)<=[forms]![frmreports]![TDate]) AND ((QryStockAvailableSub.PRunTot)>[TSale]))
UNION SELECT Purchase.BatchNo, Purchase.PDate, Products.ProductDesc, Purchase.PQty AS PurchasedQty, Purchase.PPrice, Purchase.PRunTot AS AvailableQty, ([AvailableQty]*[PPrice]) AS StockValue
FROM Products INNER JOIN (Purchase LEFT JOIN Sales ON Purchase.Product = Sales.Product) ON Products.ProductId = Purchase.Product
WHERE  (Sales.Product Is Null AND Purchase.PDate<=[forms]![frmreports]![TDate]);

I named this query: QryStockAvailableWithAndWithoutSales

You have to change the record source of the Report: StockReportOnFifoBasis

From: QryStockAvailable

TO : QryStockAvailableWithAndWithoutSales

If you are unclear with this process, let me know, and I'll send a new database with the changes.

It is important to test with some sample data before making this a permanent change.

Amazing, thanks. I'll test this tomorrow and get back to you. Much appreciated.

[writeprivate]

Amazing, thanks. I'll test this tomorrow and get back to you. Much appreciated.

Thank you so much.



FEEDBACK ON NEW QUERY "withandwithoutsales"

1. Works really well and now shows the stock of all products regardless if they have a sale - I am in awe of your hand-written SQL Union query

2. As a result of the report now showing, I have discovered a separate bug (See Screenshot below) - if you only sell one unit, then the query (yours and the original) ignores that single unit sold and shows Available Quantity as everything purchased. FWIW, I think this might be because of:

• the QuantityStockAvailable Qry expression criteria on Purchase Running Total needing to be > [TSale], or
• the popn expression on the QryStockAvailableSub
• I don't want to fiddle with these as I don't really understand what these do and don't want to shift a problem to elsewhere

A RELATED REQUEST IF POSSIBLE

3. As an aside, is it possible to see a Union query in the design query grid view? I am trying to reuse the QryStockAvailableWithAndWithoutSales query elsewhere as "PortfolioHoldingQuery:

• where the Portfolio table "id" is equal to [Forms]![frmPortfolios]![id]
• the Portfolio table is connected to the QryStockAvailableSub query via the Sales table per the screenshot
• I attached below the existing PortfolioHoldingQuery which I would need to update with the new withandwithout sales output.
  
  • Also attached is that SQL (apologies for the field name changes, please use your version and i can update)
  • Also attached is the general relationships diagram for context

SELECT QryStockAvailableSub.batch_number, QryStockAvailableSub.purchase_date, Products.product_name, QryStockAvailableSub.purchase_quantity AS [Purchased Qty], QryStockAvailableSub.purchase_price, IIf([TSale]>[popn],[QryStockAvailableSub]![purchase_running_total]-[TSale],[purchase_quantity]) AS AvailableQty, [AvailableQty]*[purchase_price] AS StockValue, Products.product_symbol, Portfolios.idFROM Portfolios INNER JOIN (Sales INNER JOIN (Products INNER JOIN (QryTotalSales INNER JOIN QryStockAvailableSub ON QryTotalSales.fk_so_product_id = QryStockAvailableSub.fk_po_product_id) ON Products.product_id = QryTotalSales.fk_so_product_id) ON Sales.fk_so_product_id = QryStockAvailableSub.fk_po_product_id) ON Portfolios.id = Sales.sales_portfolio_id
WHERE (((Portfolios.id)=[Forms]![frmPortfolios]![id]) AND ((QryStockAvailableSub.purchase_running_total)>[TSale]));

[orange]

writeprivate,

Here are a couple of graphics mentioned in PM.
You can not attach files with a PM.

Sample BatchAllocation of a singleunit /1 of Product.




Revised frmReport with Check Available Product Button and Result

[writeprivate]

Amazing - showing that single stock sale is such a big obstacle
The current gross availablility is a lovely touch. Thanks as always.

[orange]

writeprivate,

If you want to pass your evolving database to me with some instructions of what you have and what you are trying to solve/resolve/design, I will look at it.
Best to put it into a zip file.

[writeprivate]

Very kind - thanks, i will do that. I am just fixing a bug that has come up as I have been tweaking and i want to repair before I send to you.

[writeprivate]

Hi Orange, I have sent you a PM with a link to the db - thanks

[orange]

Looking at your database.
Had issues with form filling and purchased Msoft for 1000 not 100 --my fat finger mistake.
Stepped through the button click routine and finally got the BatchAllocation report to work with unit sale.

For longest time I could not get a value in Purchase (purchase_running_total)??

I added a new Module1 and renamed the previous as _Orig where I prefixed procedures with "X", since they would be seen as duplicates/ambiguous

[orange]

For writeprivate





If you run the query by itself, you should see this result(based on my Purchase and sales)

batch_number	purchase_date	product_name	Purchased Qty	purchase_price	AvailableQty	StockValue
1	15-Feb-23	Microsoft	3	1000	2	2000

Good luck.

[writeprivate]

AMAZING - it works beautifully - that missing "=" was the issue. Thank you so much