how do I create supervisor hierarchy with the access table

I am trying to create a supervisor roll up table with the employee data
information. My table currently has employee id, employee name, supervisor id
and supervisor name. I am trying to create an organizational roll up up to 4
levels. E.g. Employee John Doe is reporting to Jim Smith who reports to Steve
Williams who reports to Jack Martin. I am trying to create a table such as
below through a query and not having much success. Any help will be much
appreciated.

Employee Name Supervior 1 Supervisor 2 Supervisor 3

John Doe Jim Smith Steve Williams Jack Martin

Your design is not normalized and will give lots of problems when you try to
use it. You need something like:
EmployeeId
ReportsToId
where ReportsToId refers to another EmployeeId
the top guy has ReportsToId set to null (the buck stops here)
This arrangement allows unlimited levels.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Helen Shah" wrote:

I am trying to create a supervisor roll up table with the employee data
information. My table currently has employee id, employee name, supervisor id
and supervisor name. I am trying to create an organizational roll up up to 4
levels. E.g. Employee John Doe is reporting to Jim Smith who reports to Steve
Williams who reports to Jack Martin. I am trying to create a table such as
below through a query and not having much success. Any help will be much
appreciated.

Employee Name Supervior 1 Supervisor 2 Supervisor 3

John Doe Jim Smith Steve Williams Jack Martin

Helen,

Sounds like you are missing a *joiner* table...

tblEmployeeSupervisor
esEmployeeID (FK)
esSupervisorID (FK)

If in fact, you already have a table like the above then perhaps you need
this function...

http://allenbrowne.com/func-concat.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Helen Shah" wrote in message
...
I am trying to create a supervisor roll up table with the employee data
information. My table currently has employee id, employee name, supervisor
id
and supervisor name. I am trying to create an organizational roll up up to
4
levels. E.g. Employee John Doe is reporting to Jim Smith who reports to
Steve
Williams who reports to Jack Martin. I am trying to create a table such as
below through a query and not having much success. Any help will be much
appreciated.

Employee Name Supervior 1 Supervisor 2 Supervisor 3

John Doe Jim Smith Steve Williams Jack Martin