Stock Calculation Issue

[joker78]

Hello Everyone..

Just wonderd if anyone could shed some light on an issue i am having with calculating stock levels (quantity on hand) for products in my database. I will try to explain as best as i can..

My approach to calculating a stock level is similar to the Allen Browne theory.

I am currently using a Stock take table, Orders Received table (stock received from suppliers) and a Sales table to produce the result using Queries, for example imagine we have 1 product which is Dog food (ID = 1)

Product ID = 1

Sum of Quantity At Stock Take = 10
+
Sum of Quantity Received in orders = 10
-
Sum of Quantity sold = 5

= 15 left in stock

I have this part working fine, it is showing up in a report perfectly, for each month, with a stock take happening on the 1st of each month as a starting point.

The ISSUE is if we have 10 cans of dog food at the stock take, but none are received in any orders and 3 have been sold, the report does not show the dog food quantity which should be 7 in this case.

I know that this is because if none were received then the calculation cant add any quantity received because non were received.

Im not sure if im working this out incorrectly by using queries but just cant seem to figure it out.

I hope i made sense of the situation, and im sorry for the BIG post but any support would be very much appreciated.

Cheers, Scott.

 

[Mr. B]

I assume you are doing this in a query. Do you have the calculation sectioned with parentheses to cause it to calculate exactly as you expect it to? Like:

(Sum of Quantity At Stock Take = 10 + Sum of Quantity Received in orders = 10) - Sum of Quantity sold = 5

If you do not have the parentheses in place, then in the situation you described the calculation could be being interpreted as trying to subtract the "Sum of Quantity sold" from the "Sum of Quantity Received in orders" before adding the "Sum of Quantity At Stock Take".

Just a thought.

 

[joker78]

Hi Mr.B thanks for your reply.

The way it is done is a query which shows all received stock for the current month and year, this query uses a field called "TotalReceived" which is from the Orders received table and is totaled by Sum in the query. This gives me a total number for each product received for the current month and year.

Another query is used which shows all sales for the current month and year, uses a field from the SalesDetail table called "TotalUnitsSold", again totaled by Sum in the query. This gives me a total number for each product sold for the current month and year.

These fields are then used in a report which shows:

Product id, Product Name (Products Table)
TotalReceived (Total Received Query)
TotalUnitsSold (Total Sales Query)
QuantityStockTake (StockTake Table)

The i use a calculated text box on the report which contains this exact experssion:

=[QuantityStockTake]-[TotalUnitsSold]+[TotalReceived]

I have also tried

=[QuantityStockTake]+[TotalReceived]-[TotalUnitsSold]

Both have the same result.

 

[Mr. B]

I see what you are doing, but you do not have any parentheses in your calculations to insure that the calculations are computed in the correct order. As you know, the order in which calculations are performed certainly do make a big difference in the end result.

By the way, you said what you expected the result to be but you did not say what result you are actually getting.

 

[Simon_MT]

I use this technique for Payments against Invoices but the same applies for your scanario.

What is missing is there are no Orders. So you have to make Orders show Zero. So you need, I use another Query with a Left Join from Stock to Orders and create an expression

OrderTotal: Sum(IIf(IsNull([Order_StockID]),0,[Order_Qty]))

In this query I put the StockID in the query so any Product without an Order shows Zero and those Products with Orders will be evaluated. You will always get a match for every Product.

Simon

 

[joker78]

Thanks again for the replies, Mr.B what I wanted to see was a result even if no orders had been received but a sale had taken place.

Simon: I had thought about that kind of thing logically but just didn't know how to execute it in access. I will try your suggestion when I'm back at my home PC.

cheers again lads

 

[joker78]

in addition to Mr.B what happens is that if no order was received but I had 10 in stock from stock take and sold 3 this does not show in the report

 

[joker78]

Right, this is so hard to explain as my skills in actual SQL are poor.

Simon, i have tinkered around with your suggestion and it works really well, i have it displaying every item of stock, with a total of what has been received and if no "order received" record is present, it displays a zero ( just what i wanted) so thanks a lot for that one!.

BUT lol... what i cant do is have it display the same result as above limited to the current month, again i will try to explain as best as i can.

What i want is some kind of final output which shows me:

All Products that have been received in the current month, even if no "order received" record is present i still want it to tell me

This code below shows me every product but displays an order total for every month. I have tried adding the current month part but just keep getting errors and its to complicated for me.

SELECT TBL_Stock.ProductID, Sum(NZ([QuantityReceived],0)) AS OrderTotal
FROM TBL_Stock LEFT JOIN TBL_StocksOrderd ON TBL_Stock.ProductID = TBL_StocksOrderd.ProductID
GROUP BY TBL_Stock.ProductID;

This is the stuff ive tried to add to get it to display results ONLY from the current month:

SELECT TBL_Stock.ProductID, Sum(NZ([QuantityReceived],0)) AS OrderTotal, Month([TBL_Orders].[ReceiveDate]) AS [Month Number], Year([ReceiveDate]) AS [Year]

FROM TBL_Stock LEFT JOIN TBL_StocksOrderd ON TBL_Orders.OrderID = TBL_StocksOrderd.OrderID

GROUP BY TBL_Stock.ProductID, Year([ReceiveDate]), Month(ReceiveDate)

HAVING (((Year([ReceiveDate]))=Year(Now())) AND ((Month([ReceiveDate]))=Month(Now())));

I know this is probably all wrong, it just gives me an error which says "Syntax error in JOIN operation"

 

[joker78]

Also if im not making any sense and you are sat thinking "what is he talking about!?" Then i will try to put it as simple as i can because i think it's quite hard to explain.

I want to calculate quantity on hand or "whats left in stock" for every product in my database, following these rules:

Stock take value + Any stock received from orders (current month) - Items sold (current month)

And I want to be able to see everything, including:

If an item has NOT been received on an order but HAS had 1 or more sales
If an item has NOT been received on an order and HAS NOT had 1 or more sales
If an item has HAS been received on an order but HAS NOT had 1 or more sales
If an item has HAS been received on an order and HAS had 1 or more sales

Please bear in mind that there will always be a stock take value as this is input on the 1st of every month by the user.

so i need a calculation/formula/exspression/query whatever you want to call it which complies with all those "scenarios" listed above.

Sorry to bug you all with this, but im pretty sure my brain stopped working about 4 days a go....

Cheers again, Scott.

 

[joker78]

Cheers anyway fellas, Thanks for your help, i appreciate it.

 

[Beetle]

Did you solve your issue?