Somentimes MS Access is very annoying especially when you would you like updating a table of values and your query refer outher tables, in my case this query use two table.
UPDATE Archive SET Archive.IDTEV=(SELECT TabTessV.ID FROM TabTessV WHERE Archive.CODTEV=TabTessV.CODE)
run it and surprise showing this message:
Ok, Access don’t support this query, change with:
UPDATE Archive SET IDTEV=(SELECT TabTessV.ID FROM TabTessV INNER JOIN Archive ON Archive .CODTEV=TabTessV.CODE)
Yet problem:
I am trying this query:
UPDATE Archive INNER JOIN TabTessV ON Archive .CODTEV=TabTessV.CODE SET Archive .IDTEV=TabTessV.id
Magically it works, why??? This because Access is not able to evaluate a subquery.
Leave a comment