MS Access and self reference table

I have a table of employees.  There is a employee supervisor column which points to the employee record of the supervisor.  So, the table references itself.  The table has a dept column.   if the dept of the supervisor changes, I want to change the dept for all employees that have that supervisor.   I would know how to do this using SQL server, but I can't get it to work using MS Access.
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query

UPDATE tblEmployees INNER JOIN tblEmployees AS tblEmployees_1 ON tblEmployees.Supervisor = tblEmployees_1.EmployeeID SET tblEmployees.Dept = [tblEmployees_1].[Dept]
WHERE (((tblEmployees_1.EmployeeID)=5));

you have to place the EmployeeID of the supervisor in the criteria
0
 
hnasrConnect With a Mentor Commented:
In general:

UPDATE Employee As e INNER JOIN Employee As s ON e.SupervisorID = s.EmployeeID
SET e.Dept = s.Dept
0
 
Rey Obrero (Capricorn1)Commented:
;-)
0
 
HLRosenbergerAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.