Updating field from another table.

[Guest]

I have tblSheet1 that has fresh data that I would like to use to append to
tblEmployee.
tblEmployee has L_NAME, F_NAME, RoomID, Room, and tblEmployee.
tblSheet1 has the same except it doesn't have RoomID. It only has a Room
number field.
I want to update tblEmployee with the L_NAME & F_NAME to match the Room
number. How do I update while maintaining room number integrity when there
is not a relationship

 

[Michel Walsh]

Hi,



Something LIKE this (make a backup or try on dummy data)

UPDATE employee INNER JOIN sheet1
ON employee.FirstName=sheet1.FirstName AND employee.LastName =
sheet1.LastName
SET employee.Room = sheet1.Room
WHERE employee.Room Is Not Null


Remove the WHERE clause is you want to update (write over) data already
supplied.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I did as you suggested. The query did create a list of names, but it deleted
all of the user names when I ran it.

 

[Michel Walsh]

Hi,


In my example, sheet1 is the table with the information and employee is the
table to be updated, but that actually has nulls in it. Sine sheet1 never
appears at the left of an = after the SET clause, the basic data should
not have been be "deleted" (replaced with nulls)... unless the two tables
have been "exchanged". Be sure you SET the tables such that the table to be
updated is at the LEFT of the = as in:

SET employee.roomID = sheet1.roomID

which update employee, NOT sheet1.



Hoping it may help,
Vanderghast, Access MVP