Update of stock quantity

[andymartin3186]

Hi Everyone,
I'm sure this has been asked before so if needed please just point me to a video or thread that I need to study.
I have a job form where I can search for parts from a parts table, and add them to the job (jobparts table).
In the screenshot the top subform is a search lookup from the parts table and when you click the far right button it inserts them into the bottom subform which is the jobparts. You can then enter in a qty used. That all works great.
The thing I can't get my head round is keeping the qty's correct. If I for example add in qty 1, I want it to deduct this value from the stock qty, and I have had this working. However if I change the qty used to 2, the code I written was obviously too basic, and it then deducted a further 2 from the qty, instead of just a further 1.
Similarly if I change the qty used from 2 to 1, it should readd 1 back into the stock qty.
I can understand that I need to look at the initial value, the new value and then take the difference, but I'm struggling putting this into code.
Could anyone help me or point me in the right direction?

 

[arnelgp]

the normal approach is you don't save the Stock quantity on the table.
you add them up (from the beginning inventory + qty received - qty issued).

in your case, since you are saving it to the Stock Qty field, add this formula to the BeforeUpdate (or maybe AfterUpate event):

[Stock Qty] = [Stock Qty] + [Qty].OldValue - [Qty]

 

[andymartin3186]

the normal approach is you don't save the Stock quantity on the table.
you add them up (from the beginning inventory + qty received - qty issued).

in your case, since you are saving it to the Stock Qty field, add this formula to the BeforeUpdate (or maybe AfterUpate event):

[Stock Qty] = [Stock Qty] + [Qty].OldValue - [Qty]

Amazing!!!
Thank you so much

 

[Mark_]

I'll second Arnel's comment on how this is normally done. Avoids any issue of "people playing games" when trying to calculate remaining inventory.

 

[June7]

Review http://allenbrowne.com/AppInventory.htm

 

[GPGeorge]

And I'll add additional support to not calculating and storing values in tables. It creates a higher risk of data anomolies. (In plain words, wrong data being used in reports.)

Although it is possible to use a calculation as provided, good database design would not avoid it.

We used the Allen Browne method in Northwind Developers Template. You would do well to download and study that.

GIven that there have been reported problems with reinstantiating the NW Developers version form the current template, I keep a version on my downloads site.

https://www.gpcdata.com/downloads/NorthwindDev_Clean_2_4.zip

 

[MarkK]

The HUGE advantage of calculating inventory? If you can calculate it for today, you can also calculate it for next Tuesday.

Say right now you are out of ProductA, but you have a Purchase Order for ProductA scheduled to arrive next Monday. A customer calls to place an order for ProductA to pick up next Tuesday. What information do you need?

• Sometimes you need to know what you have right now.
• Plenty of times you need to know what you will have next Thursday, after the PO arrives on Monday, and after someone already ordered some for Tuesday.

 

[GPGeorge]

In Northwind Developers, we carry products in open Customer Orders in three statuses: Allocated, No Stock and On Order.

You'll find the calculations in the download for Northwind Developers edition.

 

[andymartin3186]

Thank you for the help on this.
As my system is dealing with services rather than supply of goods its a little different to a system that orders goods and ships them out.
For the interim I think the existing system will suffice with how it is designed.

One question I would like to ask is, if a component is added to a job and the qty added is greater than stockqty, it currently updates to a negative value which makes sense. What I would like to do is take that negative value, keep it at 0 and take the difference and place it into another field which is qtyonorder. This would then be used to update a purchase order query

Could anyone talk me through the logic in order for me to do this? The qtyon order field is already implemented. Each component has a qtyinstock, reorderlevel and reorderqty (this then populates a purchase order table which pulls together components with qtyinstock =< reorderlevel and also job parts which have a qty on order >0)

 

[GPGeorge]

As you've been advised before in this discussion, is risky to create and store calculated values in tables. Adding another such calculation in the form of a qtyonorder field is a step further down that path. What happens is that stored calculations then freeze in place values that can change later due to other updates, but don't get updated in that calculated place. You now have two different versions of the truth in your own database and no easy way to find and deal with them. Bad business decisions, based on erroneous data, can follow. Your users will eventually lose confidence in your reporting.

Moreover, this is a workaround that adds processing steps that wouldn't even need to be completed in a set of properly designed tables.

"What I would like to do is take that negative value, keep it at 0 and take the difference and place it into another field which is qtyonorder. This would then be used to update a purchase order query"


You are free, of course, to implement any design pattern you choose. Asking others to help you, though, is akin to the reckless challenge, "You think that was impressive? Here hold my beer."

I prefer not to be the beer-holder in that scenario.

Instead, I encourage you to review the solutions offered to you by others. We've all been building successful Access relational database applications for decades. Our advice comes from experience, and a lot of that experience came from making mistakes just like this.

Before you choose to proceed, though, I suggest you at least look at the solution in Northwind Developer. It is an implementation of the process Allen Browne described more than two and a half decades ago.

 

[gemma-the-husky]

If you have a lot of transactions though, one thing you can do is for individual (or all) products is undertake stock counts, and adjust your records to store an updated starting point for the stock calculation.

if you struck a balance at say 1st August 2025, then you only have to sum 1 months movements to get your actual stock level.

 

[Gasman]

Thank you for the help on this.
As my system is dealing with services rather than supply of goods its a little different to a system that orders goods and ships them out.
For the interim I think the existing system will suffice with how it is designed.

One question I would like to ask is, if a component is added to a job and the qty added is greater than stockqty, it currently updates to a negative value which makes sense. What I would like to do is take that negative value, keep it at 0 and take the difference and place it into another field which is qtyonorder. This would then be used to update a purchase order query

Could anyone talk me through the logic in order for me to do this? The qtyon order field is already implemented. Each component has a qtyinstock, reorderlevel and reorderqty (this then populates a purchase order table which pulls together components with qtyinstock =< reorderlevel and also job parts which have a qty on order >0)

So what do you want to do if you find you are out of stock by say...5, and you already have 40 on order?

 

[andymartin3186]

Hi All,
As always thank you for your support and guidance.
So, to clarify, I shouldn't be storing "qtyinstock" inside the component table, instead I should create another table called stocktake, create a relationship with the components table and store the qtyinstock in this table along with the date of the stocktake?

I'm quite a novice at this so picking everything up as I go, thank you.

 

[GPGeorge]

If you have a lot of transactions though, one thing you can do is for individual (or all) products is undertake stock counts, and adjust your records to store an updated starting point for the stock calculation.

if you struck a balance at say 1st August 2025, then you only have to sum 1 months movements to get your actual stock level.

That is the Allen Browne method we keep suggesting as a model to follow. See Northwind Developer for a more recent implementation of it.

 

[GPGeorge]

Hi All,
As always thank you for your support and guidance.
So, to clarify, I shouldn't be storing "qtyinstock" inside the component table, instead I should create another table called stocktake, create a relationship with the components table and store the qtyinstock in this table along with the date of the stocktake?

I'm quite a novice at this so picking everything up as I go, thank you.

Exactly.

One more time, though, there is an example all ready for you to study and explore.

It's in an Access template called Northwind Developer. Because some people have reported problems instantiating the database from the template, I put a reinstantiated accdb on my website. You can download it from here.

Study how we implemented inventory allocation and stock takes in that sample database. It illustrates the tables you need, including the stocktake table. It illustrates the code that allocates newly received Purchase Orders to On Order and No Stock status pending orders.

It's all there.

 

[gemma-the-husky]

If you can find it, I actually think the best inventory database was a MS template I called Dharamsala Tea. I called It that because that was the first product. Just a brilliant example. The only thing I changed was to have stock movements inwards as positive and outwards as negative so you could sum the stock on hand without having to think about the sign of the transactions.

 

[Mark_]

Please note, for the type of query you are looking for, you want to have "Order level" in one table and "Stock Quantity" returned by a different query. You then calculate what you need as "Order level" - "Stock Quantity".
If you have 10 in stock, but have an order level of 20, this gives you 20-10 = 10 to order.
If you have -20 in stock, but have an order level of 20, this gives you 20- (-20) = 40 to order.

This means you don't have different calculations based on "We have them in stock" VS "We have a number to order".

 

[GPGeorge]

This is how we display in NW Developer the Qty to Reorder calculation Mark describes.

 

[Ken Sheridan]

If you have occasion to repeat orders by a customer regularly you might find the following function of interest. It firstly inserts a new row into the Orders table with the same data as an existing order, identified by passing its OrderID primary key value into the function. It then inserts however many rows are necessary into the OrderDetails table which models the many-to-many relationship type between the Orders and Items tables, updating the unit price of each item if this has changed since the previous order:

Public Function CopyMTMRel(lngOrderID As Long)

    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngNextOrderID As Long
    Dim lngItemID As Long
    Dim lngCustomerID As Long
    Dim intQuantity As Integer
    Dim curCurrentUnitPrice As Currency
    
    lngNextOrderID = DMax("OrderID", "Orders") + 1
    
    lngCustomerID = DLookup("CustomerID", "Orders", "OrderID = " & lngNextOrderID - 1)
    
    ' insert new row into Orders with today's date as order date
    strSQL = "INSERT INTO Orders(OrderID,CustomerID,OrderDate) " & _
        "VALUES(" & lngNextOrderID & "," & lngCustomerID & ",#" & _
        Format(VBA.Date, "yyyy-mm-dd") & "#)"
    CurrentDb.Execute strSQL, dbFailOnError
    
    ' return recordset of current order's rows in OrderDetails
    ' and loop through recordset, inserting a new row
    ' at each iteration of the loop, with current unit price of item
    strSQL = "SELECT * FROM OrderDetails" & _
        " WHERE OrderID = " & lngOrderID
      
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    With rst
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                lngItemID = .Fields("ItemID")
                intQuantity = .Fields("Quantity")
                ' get current unit price of item
                curCurrentUnitPrice = DLookup("UnitPrice", "Items", "ItemID = " & .Fields("ItemID"))
                
                strSQL = "INSERT INTO OrderDetails(OrderID,ItemID,UnitPrice,Quantity) " & _
                    "VALUES(" & lngNextOrderID & "," & lngItemID & "," & curCurrentUnitPrice & "," & intQuantity & ")"
                    
                CurrentDb.Execute strSQL, dbFailOnError
                .MoveNext
            Loop
        End If
    End With
    
End Function

 

[xavier.batlle]

Review http://allenbrowne.com/AppInventory.htm

Broken link. Missing an "l" at the end of the URL

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com