Organization Chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help construct a query:

I have a table that defines an organization chart, main fields are:

UserID: Text (Primary)
FName: Text
LName: Text
Supervisor: Text (Will be the UserID of Supervisor)

As the organization is several layers deep, I need to run a query that returns all subordinates at all levels that report to a particular UserID.

Thanks for your help!!!
 
Dmiller444 said:
Please help construct a query:

I have a table that defines an organization chart, main fields are:

UserID: Text (Primary)
FName: Text
LName: Text
Supervisor: Text (Will be the UserID of Supervisor)

As the organization is several layers deep, I need to run a query that returns all subordinates at all levels that report to a particular UserID.

Thanks for your help!!!
Click to expand...

Hi D,

Here might be one method as illustrated
by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

Itzik Ben-Gan looks at it from SQL Server (but uses triggers):
http://www.winnetmag.com/Windows/Articles/ArticleID/8826/pg/1/1.html

Imagine we start with org like
http://www.winnetmag.com/Files/09/8826/figure_01.gif

and we had a "tblOrg"

UserID Text(10) (pk)
FName Text(50)
LName Text(50)
Supervisor Text(10) (UserID of Supervisor)
Depth Long
Lineage Text(255)

In our example UserID will always be full 10 chars
("9 number chars" is used by ISBN to describe "all
the books in the world" <cough>), so left-padded
with zeroes. To keep it simple, we will keep all fields
in one table rather than have Employee/Tree tables.

In this case, our lineage could go "22 deep" w/o
overflowing 255-char field (if I multiplied correctly).

So initially, our table representing org chart above
might look like (I won't fill in "LName" here):

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy
0000000002 Andrew 0000000001
0000000003 Janet 0000000001
0000000004 Margaret 0000000001
0000000005 Steven 0000000002
0000000006 Michael 0000000002
0000000007 Robert 0000000003
0000000008 Laura 0000000003
0000000009 Ann 0000000003
0000000010 Ina 0000000004
0000000011 David 0000000007
0000000012 Ron 0000000007
0000000013 Dan 0000000007
0000000014 James 0000000011

following his method:

qryRoot:

UPDATE tblOrg As T
SET
T.Lineage = '/',
T.Depth = 0
WHERE
(((T.Supervisor) Is Null));

after qryRoot:

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy 0 /
0000000002 Andrew 0000000001
0000000003 Janet 0000000001
0000000004 Margaret 0000000001
0000000005 Steven 0000000002
0000000006 Michael 0000000002
0000000007 Robert 0000000003
0000000008 Laura 0000000003
0000000009 Ann 0000000003
0000000010 Ina 0000000004
0000000011 David 0000000007
0000000012 Ron 0000000007
0000000013 Dan 0000000007
0000000014 James 0000000011



qryOnePass:

UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.Supervisor = P.UserID
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[Supervisor] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

after pass 1 (updates 3 rows):

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy 0 /
0000000002 Andrew 0000000001 1 /0000000001/
0000000003 Janet 0000000001 1 /0000000001/
0000000004 Margaret 0000000001 1 /0000000001/
0000000005 Steven 0000000002
0000000006 Michael 0000000002
0000000007 Robert 0000000003
0000000008 Laura 0000000003
0000000009 Ann 0000000003
0000000010 Ina 0000000004
0000000011 David 0000000007
0000000012 Ron 0000000007
0000000013 Dan 0000000007
0000000014 James 0000000011

after pass 2 (updates 6 rows):

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy 0 /
0000000002 Andrew 0000000001 1 /0000000001/
0000000003 Janet 0000000001 1 /0000000001/
0000000004 Margaret 0000000001 1 /0000000001/
0000000005 Steven 0000000002 2 /0000000001/0000000002/
0000000006 Michael 0000000002 2 /0000000001/0000000002/
0000000007 Robert 0000000003 2 /0000000001/0000000003/
0000000008 Laura 0000000003 2 /0000000001/0000000003/
0000000009 Ann 0000000003 2 /0000000001/0000000003/
0000000010 Ina 0000000004 2 /0000000001/0000000004/
0000000011 David 0000000007
0000000012 Ron 0000000007
0000000013 Dan 0000000007
0000000014 James 0000000011

after pass 3 (updates 3 rows):

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy 0 /
0000000002 Andrew 0000000001 1 /0000000001/
0000000003 Janet 0000000001 1 /0000000001/
0000000004 Margaret 0000000001 1 /0000000001/
0000000005 Steven 0000000002 2 /0000000001/0000000002/
0000000006 Michael 0000000002 2 /0000000001/0000000002/
0000000007 Robert 0000000003 2 /0000000001/0000000003/
0000000008 Laura 0000000003 2 /0000000001/0000000003/
0000000009 Ann 0000000003 2 /0000000001/0000000003/
0000000010 Ina 0000000004 2 /0000000001/0000000004/
0000000011 David 0000000007 3 /0000000001/0000000003/0000000007/
0000000012 Ron 0000000007 3 /0000000001/0000000003/0000000007/
0000000013 Dan 0000000007 3 /0000000001/0000000003/0000000007/
0000000014 James 0000000011

after pass 4 (updates 1 row):

UserID FName LName Supervisor Depth Lineage
0000000001 Nancy 0 /
0000000002 Andrew 0000000001 1 /0000000001/
0000000003 Janet 0000000001 1 /0000000001/
0000000004 Margaret 0000000001 1 /0000000001/
0000000005 Steven 0000000002 2 /0000000001/0000000002/
0000000006 Michael 0000000002 2 /0000000001/0000000002/
0000000007 Robert 0000000003 2 /0000000001/0000000003/
0000000008 Laura 0000000003 2 /0000000001/0000000003/
0000000009 Ann 0000000003 2 /0000000001/0000000003/
0000000010 Ina 0000000004 2 /0000000001/0000000004/
0000000011 David 0000000007 3 /0000000001/0000000003/0000000007/
0000000012 Ron 0000000007 3 /0000000001/0000000003/0000000007/
0000000013 Dan 0000000007 3 /0000000001/0000000003/0000000007/
0000000014 James 0000000011 4 /0000000001/0000000003/0000000007/0000000011/

As he mentions, these "passes" could have been
wrapped in a loop, say...for Access VBA (DAO):

CurrentDb.Execute "qryRoot", dbFailOnError

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

Adapting his example to get indented list,

qryIndentedList:

SELECT
Space([T].[Depth]*4) & [FName] AS Emp
FROM tblOrg AS T
ORDER BY
[Lineage] & [UserID];

producing:

Emp
------
Nancy
Andrew
Steven
Michael
Janet
Robert
David
James
Ron
Dan
Laura
Ann
Margaret
Ina

One way to get subordinates of a UserID

SELECT
[Enter UserID] AS BossUserID,
(SELECT t.FName FROM tblOrg AS t
WHERE t.UserID = [Enter UserID]) AS Boss,
T.FName AS SubOrdinate,
T.Depth,
[Lineage] & [UserID] AS Sort
FROM tblOrg AS T
WHERE
(((T.Lineage) Like '*' & [Enter UserID] & '*'))
ORDER BY [Lineage] & [UserID];

if entered "0000000007"

BossUserID Boss SubOrdinate Depth Sort
0000000007 Robert David 3 /0000000001/0000000003/0000000007/0000000011
0000000007 Robert James 4 /0000000001/0000000003/0000000007/0000000011/0000000014
0000000007 Robert Ron 3 /0000000001/0000000003/0000000007/0000000012
0000000007 Robert Dan 3 /0000000001/0000000003/0000000007/0000000013

notice how the padding helps "text numbers"
sort as "numbers" -- this should make a report
easy to design.

btw, if you were going to use a "Tree" table,
one simple method to "pad":

Right("0000000000" & [UserID], 10)

Please respond back if I have misunderstood.

Good Luck,

Gary Walter
 
Gary gave you some interesting links that I'm going to check out myself.

Another approach would be to use Visio. It has a wizard for taking data from
a text file, an Excel sheet, an Access table/query, or any ODBC source and
turning it into an org chart with just a few clicks. It is an exercise in a
Visio class I teach and it is truly slick. It even has options to break the
chart up at differently levels, make multiple pages, hide and expand levels,
etc.

Good luck.

Sco

Dmiller444 said:
Please help construct a query:

I have a table that defines an organization chart, main fields are:

UserID: Text (Primary)
FName: Text
LName: Text
Supervisor: Text (Will be the UserID of Supervisor)

As the organization is several layers deep, I need to run a query that
Click to expand...
returns all subordinates at all levels that report to a particular UserID.
 
Thanks Sco, I also am a Visio Freak, since the first 1.0 floppy demo. We do use visio for our org charts and facility diagrams, but needed to develop an ASP application using the supervisor lookup feature.

By the way, we love Visio 2003 featue that provides the find employee when posted to the web. We link it off of our phonebook app so users can either go to the orgchart or facility drawing and locate the person...Dave
 
Back
Top