Help needed with update query

Derevon

Registered User.
Local time
Today, 09:03
Joined
Jan 14, 2014
Messages
51
Hi everyone,

I'm using Access 2010. Whenever I try to make an update query based on another table/query I seem to get this error:

Operation must use an updatable query. (Error 3073)

Let's say I have two tables:

TbA:

Inv PO
--------------
1 2
2 5

TbB:

Inv PO
--------------
1 3
2 5

Based on the field Inv (if they're the same in both tables) I want to update the PO of TbA with the PO of TbB so I tried this:

UPDATE TbA
SET TbA.PO = (SELECT TbB.PO FROM TbB WHERE TbA.Inv = TbB.Inv);

The fields "Inv" in both tables are guaranteed to be unique, so the SELECT query shouldn't be able to retrieve more than one record.

I don't understand why I get this error. My delete and insert queries work just fine, so I'm sure there can't be anything with writing rights or something.

Any help is appreciated.

Thanks
 
Try the below query:
Code:
UPDATE TblB INNER JOIN TblA ON TblB.Inv = TblA.Inv SET TblA.PO = [tblB].[PO];
 
How about this Query?
Code:
UPDATE TbA INNER JOIN TbB ON TbA.Inv = TbB.Inv SET TbA.PO = TbB.PO;
 
The above works with some simple test tables, but not with my real table/query. Maybe there is some kind of problem with uniqueness after all,.
 
We don't know, we haven't see you data, so ...:)
 

Users who are viewing this thread

Back
Top Bottom