Help updating table with data 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

 

[John Spencer (MVP)]

Can you restate your problem?

Which field(s) do you want to update? L_NAME and F_Name? Or RoomID? Or some
other value?
What is the primary key in your table?
How are the tables related?

In tblEmployee you have a field called tblEmployee? Is that the primary key?

 

[Guest]

Thank you for repling. I am wanting to update L_NAME and F_NAME.

RoomID is the PK for tblRoom.
EmployeeID is the PK for tblEmployee
ID is PK for Sheet1

I imported the data from an excel sheet. I want to take the L_NAME and
F_NAME in Sheet1 and update the same fields in tblEmployee.

tblEmployee and tblRoom have a relationship with RoomID in both tables.

I create my other relationships with Sheet1 in the query window. Let me
know if you need more info.

I would be happy to e-mail you my Access file if that would help.

 

[John Spencer (MVP)]

ASSUMing that ID in Sheet 1 relates to EmployeeID in tblEmployee

I would guess something like the following.

UPDATE tblEmployee E INNER JOIN Sheet1 S
ON E.EmployeeID = S.ID
SET E.L_Name = S.L_Name,
E.F_Name = S.L_Name
WHERE E.L_Name <> S.L_Name
OR E.F_Name <> S.F_Name
OR E.F_Name is NULL
OR E.L_Name is Null