Updating from another table

[KjWhal]

I want to update a column in table A with the value in table B where the value in A matches another column in B

This is what I have, which seems like it would be a valid query but Access is telling me its not an updateable query.

UPDATE PaymentBackup SET PaymentBackup.ClientID = (SELECT tblClient.ClientID FROM Payment,tblClient WHERE Payment.ClientID = tblClient.ClientIDx)

 

[The_Doc_Man]

The sub-query is eating your socks here.

Do this as a JOIN between A and B and update the field in table A (using A.x syntax) from the appropriate field in table B. But DON'T try it with a subquery.

 

[KjWhal]

The sub-query is eating your socks here.

Do this as a JOIN between A and B and update the field in table A (using A.x syntax) from the appropriate field in table B. But DON'T try it with a subquery.

Is there any particular reason that sub-query is eating my socks? Thanks for the tip.