Error: Operation must use an updatable query

[andrew]

Hi

I am trying to update a table from a query with an update query. The source
query is an aggreagate query. My update query is:

UPDATE tblEquipment AS E
INNER JOIN qryComponentTotals CT
ON E.lngEquipmentID = CT.lngEquipmentID
SET
E.curCostPrice = CT.CompCost ,
E.curSalePrice = CT.CompSale
WHERE E.lngEquipmentID IN (
SELECT lngEquipmentID
FROM tblEquipmentDetails);

My source query is:

SELECT
E.lngEquipmentID,
SUM(E2.curCostPrice * ED.sngQty) AS CompCost,
SUM(E2.curSalePrice - E2.curAdjustment) AS CompSalePreAdjust,
SUM(E2.curSalePrice * ED.sngQty) AS CompSale,
SUM(E2.curSalePrice * ED.sngQty) + E.curAdjustment AS CompSalePlusAdjust
FROM (
tblEquipment E

INNER JOIN tblEquipmentDetails ED
ON E.lngEquipmentID = ED.lngEquipmentID)

INNER JOIN tblEquipment E2
ON ED.lngEquipmentID2 = E2.lngEquipmentID
WHERE ED.ysnDataType = Yes
GROUP BY
E.lngEquipmentID,
E.curAdjustment;

I get the error "Operation must use an updatable query". I am not trying to
update the query, I want to update the table using data from the query. Can
anyone tell me how to get round this?

Thanks

 

[John Spencer]

The easiest way is to create a temp table based on the source query and
then use that temporary table to make the update.

ACCESS will not let you use any non-updatable query as the source for an
update.

You other choice is to use one of the VBA domain functions to get the
information. This should work ok if you have a fairly small set of
data, but it will be slow with large amounts of data.

UPDATE tblEquipment
Set curCostPrice = DLookup("compCost","qryComponentTotals",
"lngEquipmentID=" & lngEquipmentID)
, curSalePrice = DLookup("CompSale","qryComponentTotals",
"lngEquipmentID=" & lngEquipmentID)

As always, backup your data first, in case this doesn't work the way you
wish.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[andrew]

Thanks John, I'll give the temp tables a try.