Org Chart

[adamstorch]

I have a database of 150 employees in the IS department of my company.&nbsp;&nbsp;Information on the employees include name, floor #, tel phone, etc.&nbsp;&nbsp;The layout of the department is as follows---<br><br>principal<br>vice principal<br>5 department heads.<br>managers within departments<br>other workers<br>other workers 2<br><br>I just got an organizational chart of the entire IS department, spilt into 5 sheets, they are each one of the five departments from level three of the above list<br><br>I am trying to brain storm the most efficient method of adding necessary fields/tables to my employee database so I could create reports in a hierarchical format, and I would also like to create a form in which I would be able to view employees in hierarchical form, and be able to click on the employees and access additional information on the employee such as phone number, floor number etc.<br><br><br>I really appreciate all the help I have gotten from this forum in the last two or three weeks.&nbsp;&nbsp;A lot of people on this board are really talented, and it's a great thing that fellow computer users are so eager to help each other out.&nbsp;&nbsp;I am a 20-year-old college student working as an intern in an investment firm, and this board has really helped me create some things that have impressed my supervisors.&nbsp;&nbsp;Thanks and sorry for the long post and the horrible spelling!<br>

 

[elizabeth]

I'm trying to do something similar with an inventory system that has components (parts) as well as assemblies. I don't have an answer yet but here is where I'm starting, a treeview control based on a recursive table.<br><br><A HREF="

http://support.microsoft.com/support/kb/articles/Q167/3/09.ASP"

TARGET="_new">

http://support.microsoft.com/support/kb/articles/Q167/3/09.ASP</A><br><br>I'd

be glad to get any suggestions too!

 

[RoyVidar]

Hi!<br><br>One method I've seen in theory, is using an ordinary relationship between Department and Employee (1 department may have 0 to many employees) and then use self referencing on Department (create a relationship with &quot;Department on both sides&quot;, using PK=Depno and FK=DepnoHigher...). (One department might have 0 to many &quot;Sub&quot; Departments, one department must belong to one and only one &quot;Main Department&quot;.<br><br>If this kind of thinking is appliccable in your situation, the tables might look like this;<br><br>Department: Depno, DepnoHigher, DepName ...<br><br>Employee: Empno, EmpName, Depno ...<br><br>This is a rather static solution, there might be more dynamic ways to model it, but perhaps some of the ideas might give inputs in your design process.<br><br>Good luck!<br><br>Roy-Vidar

 

[elizabeth]

I think a problem with the above would be that depts don't normally report to other departments. It's people who report to people. So the relationship would be in the Employee table. tblEmployee would include a field SupervisorID that relates back to field EmployeeID. I've seen this many times. I'm trying to work out a good way of displaying the hierarchy when there are &gt; 2 levels, which I hope the tree view control will be useful for.

 

[RoyVidar]

Having the view that excange of thoughts might trigger positive action, I post again;-)<br><br>Yes, Elizabeth, I do agree, people do report to people, but semantically I think there might be other issues involved too. I'm used to reporting to a person having the role or function as department manager, project leader etc, where this function or role is bound to the hierarchical structure of the department. Anayway, the business rules in the department, will probably give recommendations in this area of the design (where to self-reference, if...).<br><br>A self reference &quot;Supervises&quot;/&quot;Is supervised by&quot;, is very flexible, and does not need to be bound by the hierachical structure of and whithin departments. It might also give the flexibility of an employee superviced by more than one supervicor (M:N-relationship), which is a logically good design for handling situations where employees might have one primary supervisor, a couple of project supervisors etc.<br><br>Another way of approaching, is seeing the superviser as part of the department hierarchi (workers report to managers, who report to dep heads, who report to Principal/Vice Principal). This could use the previous posted employee/department structure, but might need an additional relationship between employee and department -&quot;supervisor&quot;, &quot;is the leader of&quot;... (1:1 relationship, empnoSup as FK in department).<br><br>BTW:I did try out the selfreferencing thingie last night, using Elizabeths suggestion with self-reference on Employee, which did provide a way of browsing dynamicly through the hierarchy (doing two levels at the time). It's very quick & extreemly dirty, and involves 1 tbl, 5 qry, 1 frm and 1 rpt, so if anyone is interested, post your e-mail.<br><br>Roy-Vidar