CODEWITHSUNDEEP
sqlsubqueryms-access-2007
Dtorr1981
Dtorr1981

Reputation: 263

Query to update values in a table with averages from the same table

I'm looking for a bit of support regarding using a value from a separate query in an update query. The background is that i have a query calle qry_AvgOfXCoeff which calculates the average of tbl_ConvertToDouble.XCoeff. What i would like to do is replace any Xcoeff value that is greater than 0 with the avg calculated in the first query. At present i cannot use the qry directly in an Update query as i received the dreaded 'Must use a updateable query' error.

qry_AvgOfXCoeff:

SELECT Avg(tbl_ConvertToDouble.XCoeff) AS [Avg]
FROM tbl_ConvertToDouble;

Now i've been informed that i should be able to do this by using an IN condition in the update query, but im really stumped with this one and cannot seem to find any examples of how i would implement this. I've had a play with some code as per below, but please can someone help with this. It seems such a simple thing.

UPDATE qry_AvgOfXCoeff, tbl_ConvertToDouble SET tbl_ConvertToDouble.[Xcoeff]
WHERE  (( ( tbl_ConvertToDouble.[xcoeff] ) IN (SELECT [qry_AvgOfCoeff].[Avg]
                                   FROM   [qry_AvgOfCoeff] AS Tmp
                                   Where [tbl_ConvertToDouble].[Xcoeff] > 0) ))
ORDER  BY tbl_calcreg.[xcoeff];

Thank you kindly in advance.

Donna

Upvotes: 1

Views: 335

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

Access offers Domain Aggregate Functions that can be helpful in avoiding the "Operation must use an updateable query" issue. In this case, you can use the DAvg() function

UPDATE tbl_ConvertToDouble 
SET XCoeff = DAvg("XCoeff", "tbl_ConvertToDouble")
WHERE XCoeff>0

Upvotes: 1

Related Questions