Update query

[Ian Sweeney]

What is the Access equivalent of

UPDATE table a
FROM table b
SET x = b.y
WHERE a.c = b.d

 

[M.L. Sco Scofield]

Without making the tables to test it, try:

UPDATE a
SET x = b.y
WHERE a.c = b.d

or

UPDATE a
INNER JOIN b ON a.c = b.d
SET x = b.y

Good luck.

Sco

 

[John Spencer (MVP)]

UPDATE A , B
SET A.X = B.Y
WHERE A.C = B.D

OR perhaps more efficient...

UPDATE A INNER JOIN B

 

[Guest]

The actual query is

UPDATE WardDateTotals INNER JOIN qryManagementCount
ON (WardDateTotals.[Ward name] = qryManagementCount.[Ward
name]) AND (WardDateTotals.Date =
qryManagementCount.Date)
SET WardDateTotals.Management =
qryManagementCount.HowMany;

When I try to run the query I get the error message:

'Operation must use an updateable query.'

 

[John Spencer (MVP)]

AHA!. This sound as if you are using aggregate functions somewhere in your
queries. Unfortunately Access doesn't allow you to use aggregate functions in
an UPDATE Query.

You can work around this by using the Domain aggregate functions DCount, DSum,
etc. (May be slow) or by using the qryManagementCount in a make table or Insert
query to make new rows. Then use that saved table in the Update query.

The actual query is

UPDATE WardDateTotals INNER JOIN qryManagementCount
ON (WardDateTotals.[Ward name] = qryManagementCount.[Ward
name]) AND (WardDateTotals.Date =
qryManagementCount.Date)
SET WardDateTotals.Management =
qryManagementCount.HowMany;

When I try to run the query I get the error message:

'Operation must use an updateable query.'

-----Original Message-----
UPDATE A , B
SET A.X = B.Y
WHERE A.C = B.D

OR perhaps more efficient...

UPDATE A INNER JOIN B

.