Select Stmt in Update Query - Operation Must Use an Updateable Query??

[kellstee]

I have 3 tables:
Orders
Shipments
Reporting

Shipments holds shipping cost (there can be multiple records per order since there can be multiple shipments per order). Primary key is Shipping ID.

Orders holds Shipping ID and Order ID.

Reporting holds Order ID.

I need to update the Reporting table with the total shipping cost from Shipments.

Here's my query:

UPDATE [Reporting] R SET Shipping =
(SELECT Sum([TotalCharges])
FROM Shipments S, Orders O
WHERE S.OrderID = O.OrderID
and O.[OrderNumber] = P.[Order Number]
Group by O.[OrderNumber])

Access throws back an error: "Operation Must Use an Updateable Query".

Any help would be greatly appreciated.

 

[lynchg]

A few questions:

Is there a one-to-one correspondence between OrderID and OrderNumber?

You have P.[Order Number] in your subquery, shouldn't that be R.[Order Number], assuming the order number field is also in the Reporting table.

Try running just the Select portion using a specific order number from the reporting table, if you get multiple TotalCharges values your update won't work because it can't decide which value to use.

 

[kellstee]

Thanks, lynchg for the advice. I found another way around my problem (I just created a temporary table), so it's working now.