Super manager

[rajalapati]

Hello Every body,

I have an requirement where i have an employee table with their
managers reported.
Let me give you structure of the employee table

Empno Empname Mgr
1 Srinivas 1
2 sampath 1
3 raju 2
4 sekhar 4
5 lakhi 4
6 vishnu 5

Now my requirenment is to show for every employee his super manager
where in the above case srinivas(1) and sekhar(4) are super manager.

it should look like

Empno Empname supermgrname supermgrid
2 Sampath srinivas
1
3 raju srinivas
1
5 lakhi sekhar
4
6 vishnu sekhar
4

Like this.can any body help me out in building this query

Thank you
Raj Deep.A

 

[Allen Browne]

Create a query using your Employee table.

Add a 2nd copy of the Employee table to the query.
Access will alias it as Employee1.

In the upper pane of query design, drag Employee1.Mgr onto Employee.Empno.
Access draws a line between the 2 tables.
Double-click this line. Access opens a dialog with 3 options.
Choose the one that says:
All records from Employee, and any matches from Employee1.

You can now drag the fields you want to show into the grid.

The technique is known as a self-join. If you wish to learn more about this,
see:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

 

[Gary Walter]

I have an requirement where i have an employee table with their
managers reported.
Let me give you structure of the employee table

Empno Empname Mgr
1 Srinivas 1
2 sampath 1
3 raju 2
4 sekhar 4
5 lakhi 4
6 vishnu 5

Now my requirenment is to show for every employee his super manager
where in the above case srinivas(1) and sekhar(4) are super manager.

it should look like

Empno Empname supermgrname supermgrid
2 Sampath srinivas
1
3 raju srinivas
1
5 lakhi sekhar
4
6 vishnu sekhar
4

PMFBI

If you can consider thinking "another way,"
here might be another method as illustrated
in SQL SERVER by Rob Volk:
http://www.sqlteam.com/item.as­p?ItemID=8866


add 2 fields to your table

Depth Long (no default)
Lineage Text(255)

create and save the following 2 queries:
(say your table called "tblOrg")

qryRoot:

UPDATE tblOrg As T
SET
T.Lineage = '/',
T.Depth = 0
WHERE
T.Mgr = T.Empno;


qryOnePass:

UPDATE
tblOrg AS T
INNER JOIN
tblOrg AS P
ON T.Mgr = P.Empno
SET T.Depth = P.Depth + 1,
T.Lineage = P.Lineage & RIGHT("0000000000" & T.Mgr, 10) & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

okay...you have 2 options now to fill in the
fields "Depth" and "Lineage"

option 1:
- run "qryRoot"
- run "qryOnePass" multiple times
until it does not update any more records

option 2:

in Access VBA (DAO), run code:


CurrentDb.Execute "qryRoot", dbFailOnError

Do While DCount("*","tblOrg","[Depth] Is Null") > 0
CurrentDb.Execute "qryOnePass", dbFailOnError
Loop

============
the point being that your table should now look like:

Empno Empname Mgr Depth Lineage
1 Srinivas 1 0 /
2 sampath 1 1 /0000000001/
3 raju 2 2 /0000000001/0000000002/
4 sekhar 4 0 /
5 lakhi 4 1 /0000000004/
6 vishnu 5 2 /0000000004/0000000005/

one way to start query you wanted:

SELECT
T.Empno,
T.Empname,
CLng(Mid(T.Lineage,2,10)) AS supermgrid
FROM tblOrg AS T
WHERE
T.Depth > 0;

producing

Empno Empname supermgrid
2 sampath 1
3 raju 1
5 lakhi 4
6 vishnu 4

which you could save, then join this
saved query with table again to get
"supermgrname" as well...

or, just use calculated join in first place:

SELECT
T.Empno,
T.Empname,
super.Empname AS supermgrname,
super.Empno AS supermgrid
FROM
tblOrg AS T
INNER JOIN
tblOrg AS super
ON
CLng(Mid(T.Lineage,2,10)) = super.Empno
WHERE
(T.Depth > 0)
AND
(super.Depth = 0);

thinking this way makes many difficult
"org problems" easier.....

the 2 fields don't necessarily have to
be in the same table, you could have
just as easily setup a "tblTree" to generate
them separate from the emp table...

 

[Gary Walter]

{possibly maybe}
even better would have been to add record
to table for "root," then change Mgr = 0 for
supermgrs

Empno Empname Mgr Depth Lineage
0 root 0 /
1 Srinivas 0
2 sampath 1
3 raju 2
4 sekhar 0
5 lakhi 4
6 vishnu 5

then run "qryOnePass" until all filled in...

I have an requirement where i have an employee table with their
managers reported.
Let me give you structure of the employee table

Empno Empname Mgr
1 Srinivas 1
2 sampath 1
3 raju 2
4 sekhar 4
5 lakhi 4
6 vishnu 5

Now my requirenment is to show for every employee his super manager
where in the above case srinivas(1) and sekhar(4) are super manager.

it should look like

Empno Empname supermgrname supermgrid
2 Sampath srinivas
1
3 raju srinivas
1
5 lakhi sekhar
4
6 vishnu sekhar
4

PMFBI

If you can consider thinking "another way,"
here might be another method as illustrated
in SQL SERVER by Rob Volk:
http://www.sqlteam.com/item.as­p?ItemID=8866


add 2 fields to your table

Depth Long (no default)
Lineage Text(255)

create and save the following 2 queries:
(say your table called "tblOrg")

qryRoot:

UPDATE tblOrg As T
SET
T.Lineage = '/',
T.Depth = 0
WHERE
T.Mgr = T.Empno;


qryOnePass:

UPDATE
tblOrg AS T
INNER JOIN
tblOrg AS P
ON T.Mgr = P.Empno
SET T.Depth = P.Depth + 1,
T.Lineage = P.Lineage & RIGHT("0000000000" & T.Mgr, 10) & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

okay...you have 2 options now to fill in the
fields "Depth" and "Lineage"

option 1:
- run "qryRoot"
- run "qryOnePass" multiple times
until it does not update any more records

option 2:

in Access VBA (DAO), run code:


CurrentDb.Execute "qryRoot", dbFailOnError

Do While DCount("*","tblOrg","[Depth] Is Null") > 0
CurrentDb.Execute "qryOnePass", dbFailOnError
Loop

============
the point being that your table should now look like:

Empno Empname Mgr Depth Lineage
1 Srinivas 1 0 /
2 sampath 1 1 /0000000001/
3 raju 2 2 /0000000001/0000000002/
4 sekhar 4 0 /
5 lakhi 4 1 /0000000004/
6 vishnu 5 2 /0000000004/0000000005/

one way to start query you wanted:

SELECT
T.Empno,
T.Empname,
CLng(Mid(T.Lineage,2,10)) AS supermgrid
FROM tblOrg AS T
WHERE
T.Depth > 0;

producing

Empno Empname supermgrid
2 sampath 1
3 raju 1
5 lakhi 4
6 vishnu 4

which you could save, then join this
saved query with table again to get
"supermgrname" as well...

or, just use calculated join in first place:

SELECT
T.Empno,
T.Empname,
super.Empname AS supermgrname,
super.Empno AS supermgrid
FROM
tblOrg AS T
INNER JOIN
tblOrg AS super
ON
CLng(Mid(T.Lineage,2,10)) = super.Empno
WHERE
(T.Depth > 0)
AND
(super.Depth = 0);

thinking this way makes many difficult
"org problems" easier.....

the 2 fields don't necessarily have to
be in the same table, you could have
just as easily setup a "tblTree" to generate
them separate from the emp table...