Refresh MSAccess table from another Access table using SQL

Question

I am trying to update an Original table with values ​​in Final. I am new to SQL, but I have been trying for two hours to modify various patterns to suit my needs. I am using Access 2007.

UPDATE Original o SET o.[Assest Description] = ( SELECT f.[Assest Description] FROM Original o, Final f WHERE o.[Assest No] = f.[Assest No]) WHERE o.[Assest No] = Final.[Asset No] 

Answer 1

I'm not sure if your select statement returns only one row. If you want to update the table using the select statement for the assignment, you must be sure that it returns only one row.

Alternatively, you may consider the following solution:

 update Original as o inner join Final as f on o.[Assest No] = f.[Assest No] set o.[Assest Description] = f.[Assest Description] 

Note that this will only work if both [Assest no] are unique keys in the Original and Final tables, and they are correctly connected.

Answer 2

try it

 UPDATE o SET o.[Assest Description] = f.[Assest Description] FROM Original o, Final f WHERE o.[Assest No] = f.[Assest No] 

Answer 3

I don’t know if it should be an answer or a comment, but since it stands alone, I write this as an answer. Access is terribly picky. If you fully specify the name of the field that you are updating, it may not work. If you do not fully qualify tables and join fields, the message "join is not supported" may appear. I say, maybe because, most likely, slightly different versions will do something else. At least that's my experience. So my answer is to start with Create-> Query Design. Select two tables and make them work correctly. Then change it to the update request and update the column (s) for which you want to update the fields from the table from which you want to update. Then switch to the SQL view and use it as a model to build your query in VBA. I had a syntactically correct SQL statement, but until I did this, and then matched qualified and unqualified field names, it definitely claimed that the CONNECTION was NOT SUPPORTED.