Organisation chart

[wmbb]

I want to build a organisation Chart out of a access database to present on a web-page.

In the database-table the people are coded with groupleader, sectionleader, analyst and the groupnumber like:

person1 groupleader
person2 sectionleader group1
person3 sectionleader group2
person4 sectionleader group3
person5 analyst group1
person6 analyst group2
person7 analyst group2
person8 analyst group3
person9 analyst group3

I want to present the organisation chart like:
********************************************************
(groupleader)
person1

(sectionleader1) (sectionleader2) (sectionleader3)
person2 person3 person4

(analyst group1) (analyst group2) (analyst group3)
person5 person6 person8
. person7 person9

*********************************************************

How can I fix this ?

 

[dhookom]

There is a version of Visio that allows you to create an org chart based on records in a table or query. It will output to a very nicely formatted web page.

I would rather spend the money on Visio than attempt to program this myself (unless I had lots of time to mess with writing code). Any solution should be designed to handle multiple groups, persons, and positions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

A few rough thoughts for you. First, it seems to me that this would be easier with a Level field, so I added one. The first suggestion uses a crosstab:

[tt]TRANSFORM First([PersonName] & " " & [jobtitle]) AS Person
SELECT [level] & " " & [jobtitle] AS JobLevel
FROM tblTable
GROUP BY [level] & " " & [jobtitle]
PIVOT tblTable.Group;[/tt]

For this to work, Level 1 is Group Leader, Level 2 is Section Leader, and thereafter each row of Analysts have to increment by one, so for your sample data, Analysts are Levels 3 and 4.

It is possible to write your own HTML in code. This next suggestion also uses levels but not in such an awkward way, the awkwardness is in the code:

Dim rs As DAO.Recordset
strFile = "C:\Documents and Settings\Fionnuala\Desktop\OrgChart.htm"
Set rs = CurrentDb.Openrecordset("Select * From tblTable Where Level=1")

Open strFile For Output As #1
Print #1, "<Table Border=1 Cellspacing=0><TR><TD ColSpan=3>" & rs!JobTitle & "<BR>" _
& rs!Personname & "</TD></TR>"

Set rs = CurrentDb.Openrecordset("Select * From tblTable Where Level=2 Order By Group")

Print #1, "<TR>"
Do While Not rs.EOF
  Print #1, "<TD>" & rs!JobTitle & "<BR>" & rs!Personname & "</TD>"
  rs.MoveNext
Loop
Print #1, "</TR>"

Set rs = CurrentDb.Openrecordset("Select * From tblTable Where Level=3 Order By Group")
  
Print #1, "<TR>"
Do While Not rs.EOF
    
    strGroup = rs!Group
    Print #1, "<TD>"
    
    Do While rs!Group = strGroup
        Print #1, rs!JobTitle & "<BR>" & rs!Personname & "<BR>"
        rs.MoveNext
        If rs.EOF Then
            GoTo EndTable
        End If
    Loop
    Print #1, "</TD>"
Loop

EndTable:
Print #1, "</TD>"
Print #1, "</TR>"
Print #1, "</Table>"
Close #1

As you can see from the above, Level 1 is Group Leader, Level 2 is Section Leader,and Analysts are Level 3.

 

[wmbb]

Thanks for your support.
I'will play around and see what is the best option for me.
I already have MS Visio so both option are available.

Thanks again.