Update query

  • Thread starter Thread starter Ian Sweeney
  • Start date Start date
I

Ian Sweeney

What is the Access equivalent of

UPDATE table a
FROM table b
SET x = b.y
WHERE a.c = b.d
 
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
 
UPDATE A , B
SET A.X = B.Y
WHERE A.C = B.D

OR perhaps more efficient...

UPDATE A INNER JOIN B
 
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.'
 
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

.
Click to expand...
Click to expand...
 
Back
Top