CODEWITHSUNDEEP
sqlvbams-accessms-access-2007
kostas
kostas

Reputation: 461

UPDATE query joining table and subselect in ACCESS

I am trying to update a field of my table1 using the following code but I receive the following error message:3073 operation must use an updateable query.

UPDATE table1 a 
INNER JOIN (SELECT COUNT(somevalue) AS Total,ID 
FROM ReadsPNPA GROUP BY ID) b 
ON a.ID=b.ID 
SET a.Total = b.Total

Any ideas? the subselect query works on its own.

Upvotes: 1

Views: 212

Answers (1)

HansUp
HansUp

Reputation: 97101

Use a DCount expression instead of a subquery to do your UPDATE ...

UPDATE table1 a 
SET a.Total = DCount('somevalue', 'ReadsPNPA', '[ID]=' & a.ID);

Access UPDATE queries are fussy. Techniques like subqueries or GROUP BY cause Access to treat the query as "not updateable". See Why is my query read-only? for more information.

You can use domain aggregate functions (such as DCount, DSum, DAvg, etc.) so that Access will treat the query as updateable.

Upvotes: 3

Related Questions