Update Query Help

[DeutchBose]

So I have two tables...Table1 has a non-unique field called 'Key'....there are over 200,000 records and upwards of 50 of them may have the same key. There are a dozen or so fields in this table.

Table2 has the same 'Key' field, but here it is unique and it is followed by a 'Vendor Name' and 'Vendor Number' field. There are about 80K records.

In Table1 I need to add the fields Vendor Name and Vendor Number and match using the 'Key' field.

This should be simple, but I'm having issues with this...how would I set up this query?

 

[Andrew Fergus]

Hi, I got this to work by adding the 2 tables into a new query, Query -> Update Query, change the table join properties to include all records from Table 1 and only matching records from Table 2, add the fields you want updated from Table 1, enter [table2name].[name] and [table2name].[number] (or whatever your actual table and variable names are) into the "Update To" section of the query (under the relevant headings), save and run.

The SQL looks like this (but use your table and field names) :

UPDATE DB_Table1 LEFT JOIN DB_Table2 ON DB_Table1.Key = DB_Table2.Key SET DB_Table1.Name = [DB_Table2].[Name], DB_Table1.[Number] = [DB_Table2].[Number];

If it still doesn't work, let us know what issues you are having.

HTH, Andrew.