Best way to setup tables for multible related data sets

[puforee]

I have been asked to set up a DB that shows employees and their managers. This will requires all managers in the management chain to be included. There
are 6 layers from the employee to the top. The data available includes: employee ID, employee name, managers ID. This data is consistent through all levels of employment.

How best to set this up is bothering me. I need to set it up so if any employee/manager moves position, or leaves, the relationships will update automatically.

So, if a 2nd level manager leaves I should be able to replace his name and ID and all relationships are updated. The complication could grow when employees/managers advance in their positions.

Does anyone have any ideas on how to set this up. Tables - 1 0r more? Type of queries?

Any help will be appreciated.

Thanks,

John

 

[dhookom]

I would consider a table of all employees and a table of positions. Each employee has a position as well as a position they report to.

[pre]tblEmployees
======================
empEmpID primary key
empFirstName
empLastName
empPosID link to tblPositions for this employee
empSupPosID link to tblPositions for supervisor



tblPositions
=======================
posPosID primary key
posTitle[/pre]

Another possible solution would have

[pre]tblEmployees
======================
empEmpID primary key
empFirstName
empLastName
empPosID link to tblPositions for this employee

tblPositions
=======================
posPosID primary key
posTitle
posReportsPosID[/pre]





Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016