Create a multi-level organisation chart / hierarchy from a single table

This self referring table can be used in a query where you place the table as many times as you have levels (+1) and join the  reportsToID to the staffID for all subsequent tables.
When filtering the first table for an empty reportsToID the start will be with the "roots".

Did you try this ?

The code solution would be to create a so-called recursive procedure taht's calling itself when a new "branch" is found.

Nic;o)

You can create a query using Reports to...  and builds a record set and do a grouping report...
You create a query that has TWO instances of the table and create the link between the ID and Reports to ID

Alias the name of the two tables   Mgr & Employee   or ReportsTo and Employee

and take the Name from the Manager as Manager  and take the Name from the Employee side as the employee

That said

Visio does have a OrgChart wizard that will take the data a build an orgchart

Rich

Nico/rich, I think you're both onto something, I have been reading a bit more about this and it seems like the ideal way of the output data is in a table like this:

Level1            Level2            Level3            LevelN
------------------------------------------------------------------------------------
FD            [NULL]            [NULL]            [NULL]
FD            FDSubordinate1      [NULL]            [NULL]
FD            FDSubordinate2      [NULL]            [NULL]
CIO            [NULL]            [NULL]            [NULL]
CIO            CIOSubordinate1      [NULL]            [NULL]
CIO            CIOSubordinate1      CIOSubSubordinate1      [NULL]
CIO            CIOSubordinate2      [NULL]            [NULL]

Because once this table/query has been set up, the report can deal with the formatting etc. I've found a similar EE question that might shed some light on the matter, I tried fishing some code out, but with little luck.

http://www.experts-exchange.com/Databases/MS_Access/Q_20768479.html

Perhaps someone can put together a query that I can use, I understand basic SQL theory/queries, but I'm afraid I'm a little lost with the SQL suggestions I've been given so far. Thanks for your patience.

The following is a simple example of the concept bringing 4 levels of org

Place 4 copies of the same table onto your SQL work area

Look at their property sheets and you will see a property called Alias

Rename each table with a simple and logical name (I used Level 1-4)

Bring the Name, (and any other field required) from each level
Rename each field with a simple and logical name (I Used LevelXName where X indicates the level)

Run the query and you see the data

Make a grouping report and you are on your way

----------------------------------------------------------------------------------------------

SELECT Level1.WPName AS Level1Name, Level2.WPName AS Level2Name, Level3.WPName AS Level3Name, Level4.WPName AS Level4Name
FROM WorkerProviders_tbl AS Level4 INNER JOIN (WorkerProviders_tbl AS Level3 INNER JOIN (WorkerProviders_tbl AS Level2 INNER JOIN WorkerProviders_tbl AS Level1 ON Level2.WPParent = Level1.WPID) ON Level3.WPParent = Level2.WPID) ON Level4.WPParent = Level3.WPID;

----------------------------------------------------------------------------------------------

Rich

Note: on the above
Each table has an ID Field 'WPID' and
Each Lower (higher number) level's WPParent points to the WPID of another record
If you are at the top level (this person reports to no one but themself...   The two fields are equal

Link each lower WPParent to the WPID of the parent table


1.WPID...2.WPParent      2.WPID... 3WPParent .....      etc

Rich

Rich, thank you for assisiting with my query. You have got my mind thinking about different ways of how we can solve this.
The problem with the above solution is that it assumes an equal length to the chains of command, ie. it only outputs the links which have n people in them (in this case 4), in addition, it fetches any link with four people, so a link with 5 people would be listed twice, first as 1-2-3-4 then 2-3-4-5.

I've done some more research and I think the answer is a recursive VB function that ties into a query and produces a table with n number of columns where n is the max number of columns. Anybody fancy having a go?

I've look at BOM (bill of materials) queries and a host of other solutions, but the general feeling is that the VB solution is the most efficient, particularly given that we don't know the number of levels.

A bit of background, the type of list I have is an adjacency list, because the connection between parent-child are adjecent to eachother. Oracle DBs have the abillity to query recursively, but MS SQL and by extention, Access does not.

Thanks again to those of your helping out.

Hmm, guess you overlooked this sentence in my comment:

When filtering the first table for an empty reportsToID the start will be with the "roots".

Nic;o)

If you use an outter join it should return the shorter chains (an oversite since I am knee deep or better into another project)



Rich

Rich, I tried OUTER JOIN, but Access rejected it. I tried RIGHT JOIN, and got an insufficent result. I don't think this can be solved with a pure SQL solution.

In other news, I have now actually managed to solve this using a VB script and a query which takes the output from the script and breaks it into n number of columns (as the VB script only outputs the path in a single column). I was hoping for a more elegant solution, either pure SQL or a single VB script do produce a multi-column query.

I'm a bit unsure of what to do with the points for this question at this stage, as I haven't really gotten the answer I was looking for, although I haven't fully explored Nico's comment (I didn't fully understand how to implement the solution). I will leave it open for a couple of days and see if someone has an ingenious solution to this issue.

Thank you for your help so far.

Just place all your tables (one for each level) and place for each the fields staffID, name and reportsToID.
Now place under the first table's reportsToID the criteria:
Is Null

This will make sure that only staff without a "reports to" will be the first visible level.

Nic;o)

I've solved this using a vb script and a couple of funny little queries, if you're here looking for the answer to my question, have a look here:

http://www.experts-exchange.com/Databases/MS_Access/Q_20768479.html

With regards to the points, I'm happy to let you guys decide who gets what. Keep me posted.

Hmm, looks very slow to me using a function with DLOOKUP(). Would be better to use a recursive recordset processing loop.
But as it's solved, just ask in CS (http://www.experts-exchange.com/Community_Support/) to delete/refund this question.

Nic;o)

Nico, do you have any code snippets? As you mentioned, the code above is indeed slow, and I'm afraid it will break once I give it the 3000+ recordset, so any improvements would be very welcome.

Either I'm doing something wrong, or that code just doesn't work. It seems to assume that every boss has to have his own line (where the boss appears as the employee). This won't work, because there will be people who are only bosses, and not "employees", for instance the CEO.

Any thoughts?

I tested with:
StaffID                    Name      ReportsToID
FD                     FD Name      
FDSubordinate1      FDSubordinate1 Name      FD
FDSubordinate2      FDSubordinate2 Name      FD
CIO                     CIO Name      
CIOSubordinate1      CIOSubordinate1 Name      CIO
CIOSubordinate2      CIOSubordinate2 Name      CIO
CIOSubSubordinate1      CIOSubSubordinate1 Name      CIOSubordinate1

Giving:
FD Name
FD Name;FDSubordinate1 Name
FD Name;FDSubordinate2 Name
CIO Name
CIO Name;CIOSubordinate1 Name
CIO Name;CIOSubordinate2 Name
CIO Name;CIOSubordinate1 Name;CIOSubSubordinate1 Name

Thought that was the intention...

Nic;o)

Nico,

You are very right, my apologies, it works like a charm, Is there a way of outputting the data to a datasheet? Or better yet, just doubleclick on a query sheet which runs the module and outputs the data in its data sheet?

Just define a table tblOutput with an autonumberID and a memo field named "Staff" and change:

   Debug.Print fncRecur(rs!staffid)

into:

   currentdb.execute ("insert into tblOutput (Staff) values ('" & fncRecur(rs!staffid) & "')")

Nic;o)

Further to Nico's comments, if you want to break the data up into different cells, I am sure there are cleverer ways of doing it, but one quick and dirty way would be to run a query which in turn runs this script:

Function SplitIt(pFullName As String, pPosition As Integer) As String
  Dim HoldArr() As String
  HoldArr = Split(pFullName, " > ")
If (pPosition - 1 <= UBound(HoldArr)) Then
    SplitIt = HoldArr(pPosition - 1)
    End If
End Function

The query should have the following in each cell:

Level1: SplitIt([Reference],1)

Where "Level1" is the title of the column, "SplitIt" is the name of the script called "[Reference]" is the name of the cell being split up (or using the names Nicohas been using replace it with "[Staff]") and finally the "1" is the part of the string (e.g. each ";" increasing the value by 1) you want to put into the cell, so that the column called Level1 would be 1, the column called Level2 would be 2 and so on, so that:

Boss;Subordinate;SubSubordinate

Becomes
Level1|Level2|Level3
Boss|Subordinate|SubSubordinate
(Where the "|" deliminates cell change)

I don't know if the above made any sense, if not, just comment here and I'll be notified. Right now I'm so hungry that I'm close to passing out.

I gave the points too quickly... The script gives an error when you introduce it to a large dataset (1600 records):

Run-time error '3048' Cannot open any more databases.

It highlights the following line:

Set rs = CurrentDb.OpenRecordset("select * from tblStaff where StaffID='" & strReportsToID & "'")

Having read up on it, it seems there is a limit of 2048 of the number of tableIDs Access can dish out. Now, I'm sure there must be a way to close the recordsets once they've been used, I don't seem to figure out where, and with what command.

Any suggestions? I'm happy to award additional points for the answer, lots of them too if I it comes quickly... :-)

sina
london