Employee hierarchy table design

[Ecreations]

Hey Guys,
I am trying to get an access table to give me a hierarchy of the employees.
Currently the table is like this
Emp_Number UniqueKey based on the employee corpID
First_Name
Last_Name
SupID ---> self Join to Emp_Number in the same table

I can do a self join to get the hierarchy but that makes no sense since I would have to update all the SupID in case a supervisor leaves the company.

anyone can recommend a better table design?

 

[traingamer]

...but that makes no sense since I would have to update all the SupID in case a supervisor leaves the company.

That makes perfect sense. The employees now report to a new supervisor.
pseudo code

update emptable set supid = newnum where supid = oldnum

Or you could have them report to a 'position' like 'line 1 supervisor' and just change the id for 'line 1 supervisor'. It depends on what fits your database. What will make easier entry / reports/ updates?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Crowley16]

use another table?

tblEmp(empID, name)

tblSup(empID, supID also an empID)

then set up a link between empID and supID with referential integrity


--------------------
Procrastinate Now!