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?

What I mean is when we an order, the quantity in the the PurchaseDetailsT table decrease, so in case one of the orders has been canceled for any reason we have to increase back the quantity in the PurchaseDetailsT table using a Cancel Order button and removed from the orderDetailsT table as well