Update Query - Operation must use an updateable query

[KeithHx]

Hi,

As an Access novice I am trying to do a simple update of a table but keep getting an annoying error:

I have 2 tables - Products and OrderDetails

Products
ProductID
Description
StockQuantity

OrderDetails
OrderID
ProductID
Quantity

Basically I want to update the Products.StockQuantity column to subtract the sum of all orders in the OrderDetails table.

So if Product A started with a StockQuantity of 5, and there are 3 Order_Details record, each with a quantity of 2, the query should update the Products.StockQuantity record to -1.

I have tried a few permutations such as:

UPDATE PRODUCTS
set PRODUCTS.StockQuantity = PRODUCTS.StockQuantity - (
SELECT SUM(OrderDetails.Quantity)
FROM ORDERDETAILS
WHERE ORDERDETAILS.ProductID = PRODUCTS.ProductID);

but I seem to keep getting the following error:
"Operation must use an updateable query"

Help - I'm tearing my hair out here!!

Thanks,

Keith

 

[KeithG]

It is not a good idea to store a calculated field. Instead you should include the calculation of your form, report, or query. This way it is alway accurate.

 

[lgedeon]

I agree that in general you should not store calculated values, however there are times when this is appropriate. For example, when you need summaries on a huge table with complicated calcs or when you are trying to work around other limitations of Access.

Keith, I am right there with you on this one. Access can get quite frustrating at times.

 

[Simon_MT]

You basically can't update the product of a total! However it you create a new field in Products LessOrderQty you can using the ProductID:

First and most importantly (via a Query)

Set the LessOrderQty = 0

Then

Set Products.LessOrderQty = Products.LessOrderQty + OrderDetails.Quantity

This is the "traditional" way of totalling.

Thinking about it the Product Quantity would be depleted everytime you applied this strategy but if you keep the field separate

You can then calculate the Balance as StockQuantity - LessOrderQty.

One would think that whilst this works for the attrition of stock what about the addition to Stock?

Whilst many don't like storing these kind of values it is a damn sight quicker to avoid large transactional tables.

Simon

 

[Atomic Shrimp]

I didn't always feel this way, but I now agree with the mainstream - storing and updating a stock figure isn't as good a solution as calculating stock based on the sum of transactions.

If you calculate stock based on a starting figure (opening stock, initially zero) minus* any subsequent transactions, it is not possible for a mismatch to occur between the stock figure and what it ought to be if you look at the transaction history.

If you store the calculated value, you are divorcing the process of stock calculation from the process of transaction generation - several things can go wrong, including:

• The stock gets updated, but for some reason, the transaction fails to append into the archive table
• The stock update fails to happen, but the transaction still appends into the archive

(*I say 'minus', because if you store your sales type transactions as positive quantities and your GRNS, credits, refunds, returns, etc as negative quantities, it just works (minus a minus is a plus))