• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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.
0
HLRosenberger
Asked:
HLRosenberger
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)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
 
hnasrCommented:
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
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now