Update query using different tables with same data item

[Alexandria]

I want to be able to use an update query to populate a field in table A from the same field in table B. I have followed the normal procedure in the query and it runs then telling me that it is about to update but there is never any data showing.

I have run a few tests and the only thing I can see that is stopping it happening is that the tables are not identical. The field I want to update is obviously in both tables and the field I am linking both tables on but that is about it. Is this the problem? Do the tables have to be identical?

Can anyone help? Just so you know I don't have a great knowledge of SQL in Access.

 

[ecniv]

See this thread... same question.

http://www.access-programmers.co.uk/forums/showthread.php?t=73074

Its to do with how you make the join... before you update..


Vince

 

[Alexandria]

Thanks so much but I am not an advanced user of Access so I wouldn't know how to apply that.

 

[ecniv]

ok.

Create a new query
Add in the table to be updated then the table with the data.
Drag the Primary key/unique id field that matches them
Double click on the black line and change to Everything from the first table and only those that match from the second.
Change query type to update.
Double click on the destination field in the first table.
Double click on the source field in the second table.
Copy the table name and paste into the set row inside square brackets (eg [tablename]).
Copy the field name and put it after the [tablename] separated by a dot ([tablename].[fieldname].
Select the source column in the output, and delete it.

This should leave you with a query which updates the main table with the second tables values.
To be safe put nz([tablename].[fieldname],<defaultvalue>) instead, so if there are no matches it puts in a default value. <defaultvalue> is -1 or "" or whatever you want, as long as the data type matches.


Vince