update table record from xls using "if"

Hi Team,

I have an XLS sheet that i have been using to key in data (using copy/paste to get the repeatitive info in quickly) that I use in the warehouse.  the xls has all the same columns that my table has.

what I need to do is a simple "find", then "update" using a command button on a form.

in particular I want to find "file1.serial_#"  in table1.serial_#, and then update table1.box_id with file1.box_id

or in english, have the button go record by record finding a match for the data in column J in the datatable column then update the "new" box id number in the table with what is in the xls column
Who is Participating?
Create a link to the Excel file.
Then you can create a simple update query.  Use your own table and link table names in the following...

Update tablename inner join  xllinkname
on tablename.[serial_#] = xllinkname.[serial_#]
Set tablename.box_id = xllinkname.box_id
KollBrianAuthor Commented:
apologies, the serial number is in column K in the sample1 excel file.
KollBrianAuthor Commented:
Thanks Peter, with your help I was able to get the update working perfectly.  Took a bit of research on the syntax but now that I have it this will make my life much easier :)
Update tbl_box_detail inner join  tbl_test_link on tbl_box_detail.[serial_#]=tbl_test_link.[serial_#]
SET tbl_Box_Detail.Box_ID=tbl_test_link.box_id, tbl_Box_Detail.PALLET_ID=tbl_test_link.pallet_id  
Where tbl_box_detail.[serial_#]=tbl_test_link.[serial_#]
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.