I had this working last night after reading a post on this site for help. Today it is not working and I confused. I imported some data from an excel spreadsheet into a new table called tbl_Supplier (field names OrderNo and Supplier). I have another table called tbl_Input with fields Supplier and OrderNo (along with other fields).

Basically I wanted to update all the blank Supplier names in tbl_Input with the Supplier names in the table tbl_Supplier using a match on the OrderNo.

UPDATE tbl_Supplier INNER JOIN tbl_Input ON tbl_Supplier.OrderNo = tbl_Input.OrderNo SET tbl_Input.Supplier = [tbl_Supplier].[Supplier];

There is data in the fields Supplier and OrderNo for the table tbl_Supplier but when I view the results they are all blank. I checked manually (search and find) and the ordernos do exist in both tables.

I thought I had it the wrong way round but if I do it the other way then it takes all the supplier names out of the tbl_supplier as the supplier name in tbl_input are all blank. Any help would be gretly appreciated, I have compacted and repaired the database just in case also.

Why don't you just append the new records into the old table?? Unless there is other data involved....

This (http://www.access-programmers.co.uk/forums/showthread.php?t=134066) might give you something too...

Thank you. Im a little new to all this.

I suppose I do want to append the suppliers name into tbl_input from tbl_supplier but there is other data involved and I only want to do it for the records where the order number match. I assume an append would just add some new records to the table?

I will look at the thread you kindly linked and see if that helps. Many thanks

I wanted to update all the blank Supplier names in tbl_Input with the Supplier names in the table tbl_Supplier using a match on the OrderNo.Jan,

you can do this the same way as in the link I provided. That will insure that all the suppliers in tblsuppliers will be updated with correct data from your tblinput column. Your code would be something like...UPDATE [tbl supplier], [tbl input] SET
[tbl input].[supplier]=[tbl supplier].[supplier]
WHERE ([tbl supplier].[orderno])=[tbl input].[orderno])
AND ([tbl input].[supplier] Is Null);