Cumulative Query

[Otacustes]

Hi,

I need some help with writing an update query in Access which will update the stock column of the table below. This is designed to determine where the stock is used and a tricky one to describe....

Here is a sample of the data

ProdID In_Stock PurchaseOrderQty PurchaseOrderNo Stock
Prod123 650 150 PO_ABC123 150
Prod123 650 300 PO_ABC124 300
Prod123 650 150 PO_ABC125 150
Prod123 650 450 PO_ABC126 50
Prod123 650 150 PO_ABC127 0
Prod123 650 150 PO_ABC128 0

Here is an explanation of how the data in the stock column is derived:

Total 650 in stock.

150 come in on PO_ABC123 so we have 150 of these in stock
300 come in on PO_ABC124 so we have 300 of these in stock (450 total = 150 + 300)
150 come in on PO_ABC125 so we have 150 of these in stock (600 total = 150 + 300 + 150)
450 come in on PO_ABC126 so we have 50 of these in stock (650 – 600)

Could someone assist with a suitable update query to fill in the stock column for each record please?

Many thanks

All help is greatly appreciated
Kind regards
Ota

 

[MajP]

150 come in on PO_ABC123 so we have 150 of these in stock
300 come in on PO_ABC124 so we have 300 of these in stock (450 total = 150 + 300)
150 come in on PO_ABC125 so we have 150 of these in stock (600 total = 150 + 300 + 150)
450 come in on PO_ABC126 so we have 50 of these in stock (650 – 600)

I cannot see the logic.
So for the first 3 records the amount in stock is simply the same as the amount that came in. So what does the total have to do with it?

On the fourth record the rule changes. The stock is no longer simply the amount that came in, but it is some value 650 minus the running total.

Can you explain the logic in words? I cannot make any sense of the rule.

 

[Otacustes]

This is the bit I struggled with for some time and needed it explaining...

The first 150 come in on PO_ABC123 so we have 150 in stock but is less than the 650 we should have in stock
The second 300 come in on PO_ABC124 so now we have 450 in stock and is still less than the 650 we should have in stock
The third 150 comes in on PO_ABC125 so now there are 600 in stock and is less than the 650 we should have in stock
The fourth 450 comes in PO_ABC126 so now there is 650 in stock which is the difference between the 650 we should have in stock and the 600 that have come in on the last four PO_s.

Hope this helps?

Thanks

All help is greatly appreciated
Kind regards
Ota

 

[MajP]

Sorry, I was confused on your names. "Stock" represents the amount of stock that can be filled on a purchase order. It is the minimum of what was requested and what is available.

I can do this but have to buid a helper function. Someone might be able to do in pure sql

Public Function getStock(PreviousOrders As Long, In_Stock As Long, orderQty As Long) As Long
  Dim Available As Long
  Available = In_Stock - PreviousOrders
  If Available < 0 Then Available = 0
  If orderQty > Available Then
    getStock = Available
  Else
    getStock = orderQty
  End If
End Function

query

SELECT 
 tblStock.ProdID, 
 tblStock.In_Stock, 
 tblStock.PurchaseOrderQty, 
 tblStock.PurchaseOrderNo, 
 Nz((select sum([purchaseOrderQty]) from tblStock as A where tblStock.ProdID = A.prodID and tblStock.purchaseOrderNo > A.purchaseOrderNo),0) AS SumPreviousOrders, 
 getStock([sumPreviousOrders],[in_stock],[PurchaseOrderQty]) AS Stock
FROM tblStock;

This will only work if you can sort on a field in the subquery. I sorted on purchaseOrderNo, but that may not be a legit field. You should have a date field or autonumber. In a database the order in which you enter data is meaningless and there is no way to ensure that order is maintained without a sortable field.

 

[Otacustes]

This looks great, thanks very much for your help

All help is greatly appreciated
Kind regards
Ota