Hierarchy report

[Guest]

Another (hopefully) easy one...I get an employee table from another
department with the following pieces of data:

deptid
mgr name
name

I now need to create a hierarchy report that will show the mgr name > name
relationship. One catch - the "name" field could or could not also manage
other "names"...so the report could look like: mgr name > (mgr) name > (mgr)
name > name. I think that maybe I'm not getting the data I need from the
department.

I know about linking the table to itself, but when I tried, I only got the
first level mgr names. To get deeper, I did some more querying on the first
query, second query, etc. Am I over-complicating things?

I've done this previously, but it was a while ago...I just need a pointer or
two to get me back in the swing of things.

Thanks in advance for any assistance.

 

[Guest]

I used limited amount of data so try this ---
SELECT ReportSmith.deptid, ReportSmith.[mgr name], ReportSmith_1.[mgr name],
ReportSmith_1.name
FROM ReportSmith LEFT JOIN ReportSmith AS ReportSmith_1 ON ReportSmith.name
= ReportSmith_1.[mgr name]
WHERE (((ReportSmith_1.[mgr name]) Is Not Null))
ORDER BY ReportSmith.deptid;

 

[Guest]

Karl,
Thanks - this worked with a few more modifications. After I posted the
question yesterday, I continued to test and found that I needed to link the
table to itself several times (5 to be exact) to get all of the requested
management levels.

I guess I need more concise information to get all of the management levels.

Thanks again.

I used limited amount of data so try this ---
SELECT ReportSmith.deptid, ReportSmith.[mgr name], ReportSmith_1.[mgr name],
ReportSmith_1.name
FROM ReportSmith LEFT JOIN ReportSmith AS ReportSmith_1 ON ReportSmith.name
= ReportSmith_1.[mgr name]
WHERE (((ReportSmith_1.[mgr name]) Is Not Null))
ORDER BY ReportSmith.deptid;

--
KARL DEWEY
Build a little - Test a little

Another (hopefully) easy one...I get an employee table from another
department with the following pieces of data:

deptid
mgr name
name

I now need to create a hierarchy report that will show the mgr name > name
relationship. One catch - the "name" field could or could not also manage
other "names"...so the report could look like: mgr name > (mgr) name > (mgr)
name > name. I think that maybe I'm not getting the data I need from the
department.

I know about linking the table to itself, but when I tried, I only got the
first level mgr names. To get deeper, I did some more querying on the first
query, second query, etc. Am I over-complicating things?

I've done this previously, but it was a while ago...I just need a pointer or
two to get me back in the swing of things.

Thanks in advance for any assistance.