best way to Calculate Qty on Hand in Database?

[siamandm]

Hello All,
I have tired to calculate the quantity on hand using query as below, but someone told me this way works for small business and small number, it will slow down when you have a large amount of orders!, could you please advice better way?

Screenshot 2024-04-22 094456.png

and

Screenshot 2024-04-22 094902.png

[HansV]

How many records do you expect? On modern PCs, Access is quite capable of calculating the stock on hand on the fly for thousands of records. It might become a problem if you have hundreds of thousands or millions of records.

[siamandm]

Thank you for the reply,
so you mean for a medium business that having 10K records 6 or less in a month will not have an issue they way i posted?
Regards

[HansV]

In my experience, they shouldn't have problems.

[siamandm]

Thank you very much

[Gasman]

I would expect large companies like Amazon, use the same method?
Perhaps ask your someone how he would do it, and keep the quantity on hand accurate all the time?

[SpeakEasy]

I would expect large companies like Amazon, use the same method?

I wouldn't expect them to be using Access ...

[siamandm]

another approach suggested by a friend, is adding QtyOnHand to table ProductT, and use an update query to deduct number from QtyOnHand
but not sure how this method is more effective or not

Screenshot 2024-04-22 120320.png

[HansV]

That method is often used if the number of records is expected to be very large. Storing the quantity on hand is more efficient than calculating it each time in that situation.
But there is a small risk that the stored quantity will become inaccurate over time, for example if a delivery or order is cancelled.
So if you do this, it is advisable to check the stock against the values stored in the database periodically, for example once or twice a year, and correct the values in the database if necessary.

[siamandm]

can we add a button to put Quantity back to the Junction table PurchaseDetailsT ?

[HansV]

Sorry, I don't understand. From where would you want to place Quantity into PurchaseDetailT? And why?

[Gasman]

I would expect large companies like Amazon, use the same method?

I wouldn't expect them to be using Access ...

Nor would I, but the logic would be the same?

[siamandm]

Sorry, I don't understand. From where would you want to place Quantity into PurchaseDetailT? And why?

let me explain what is in my mind please
lets say we have 100 quantity of item Laptops,
when we place an order of 20 Laptops , this will deduct 20 from 100,
so if we can put a "Cancel Order" button on the Order Form, when we click it, it add back the 20 laptops back to the table.

I hope you got my point
Regards

[HansV]

Perhaps

Code: Select all

Private Sub CancelOrder_Click()
    If MsgBox("Are you sure that you want to cancel this order?", vbYesNo + vbQuestion) = vbYes Then
        CurrentDb.Execute "UPDATE ProductT SET QtyOnHand = QtyOnHand + " & Me.Quantity, dbFailOnError
    End If
End Sub

[siamandm]

Thank you Dear Hans,
this works perfect,

[siamandm]

Apologies for reopening this discussion and making it lengthy,
Upon deeper thought now I'm totally agree with Hans to calculate the QtyOnHand on fly not saving it as a filed in the ProductT table, and this is because in case of two same products there will be two records for their Quantity and you will need extra works to get the total.

also I came across an article online that demonstrates how to calculate the Qty on hand using a function: http://allenbrowne.com/AppInventory.html. However, I am puzzled by the inclusion of the table tblStockTake in the database. Upon testing, it appears that this table does not store any data. Can someone please explain the purpose of this table?

InventoryRelation.gif

[StuartR]

The stock take table would be used to record how many product can be found on a physical check of the warehouse. If this is different to the expected amount then something needs to be done to investigate and correct the issue

[siamandm]

so can we get rid of it? Or it is something useful to have?

[HansV]

Most companies take stock at least once a year. Where I live, this is usually done on the 2nd of January. they count the stock on hand and compare it to the values returned by the database.

[siamandm]

Thank you dear Hans for your input
So do you recommend to have such table?