Level Organized DB - Is Query the Answer?

[James]

Thanks for all the help so far! To recap--I'm trying to
build a database giving the organizational level of
employees. A report would have to be generated that lists
the employees starting from CEO and then indented
(or "tabbed") for each subsequent level. For example:

Company1

PersonA - Chief Executive Officer
PersonE - President of Finance
PersonJ - Treasurer
PersonF - Senior Accountant
PersonZ- Junior Accountant
PersonB - Payroll
PersonD - MailRoom Guy
PersonX - President of Marketing
PersonK - Marketing Manager
PersonG - Magazine Editor
PersonZ- Adds Salesman
PersonB - Graphic Designer
And so on...

So far, I have three tables: person table, company table,
and position level table. in the person table, each person
has a field that identifies who he/she directly reports to
as well as a LevelId that corresponds to the PK in the
Position Level table. BUT... still I'm looking for help in
understanding how one might generate a report like
described above. Do I need to make a query that will list
in proper level order all the members of the company? Or
would I do multiple queries every time I encounter a
person with subordinates? confused... - James

 

[Guest]

I would suggest a recursive(re-entrant) routine. These tend to be best for
builsing tree structures

Put a button (cmdBuildTree) on a form and add this code to the click event

Sub cmdBuildTree()
BuildTree Null, 1
End Sub

This (untested) code should build a complete tree provided at least one
person has no boss. If you have used zero for this case instead of null, you
will need to change the code accordingly.

Chris
Then add this sub to the module

Sub BuildTree(ByVal PersonId as variant, ByVal nSpaces as long)

dim strSql as string
dim db as database
dim rs as recordset

if isnull(personId) then

strSql = "Select PersonId, PersonName, Position from tblPerson where
ReportsTo is null;"
Else
strSql = "Select PersonId, PersonName, Position from tblPerson where
ReportsTo =" & cstr(PersonId) & ";"
end if

set db = currentdb()
set rs = db.openrecordset(strsql, dbopensnapshot)
do
if rs.eof then exit do
debug.print space(nspaces) & rs("PersonName") & " " & rs("Position")
BuildTree rs("PersonId"), nSpaces + 1
rs.movenext
loop
rs.close
db.close
end sub