complex report / query

[jlm]

I believe that I need to create a temp table to hold data at runtime.
Can someone give me information where I can find out more about temp
tables?

basics --
a user of my Leave Tracking db can either be a supervisor or an
employee. If the user is an employee, I want to print out only their
leave data. If the user is a supervisor, I want to be able to print
out their's as well as all of their employee's leave data.

Tables are on a SQL server.

more details --
I have a "reports" form which has an OnOpen event to capture the
NTUserName, from that I can capture their UserID and their
supervisor's ID, and whether they are a supervisor or just an
employee. The temp table would hold the user's ID and any employee
IDs that this employee may have under them. I thought that by
creating a temp table to hold all employee/supervisor IDs, I could use
this to print out any cascading information on any
employee/supervisor.

If the user is a supervisor, I want to be able to loop through my
Employee table to capture all of their employees and use that list in
a query that brings up all the leave hours used and accrued (each
record in this table has EmployeeID, their SupervisorID, and a
checkbox to determine if they are a supervisor).

Some employees are supervisors have supervisors under them. In this
case, the head supervisor should be able to look at the leave hours of
their supervisors and all of their supervisor's employees.

I really hope this makes sense.

tia,
JMorrell

[Pieter Linden]

Maybe I'm misremembering, but I thought this was an adjacency list
problem. You might want to look up Joe Celko's posts on the subject
in this NG. There here somewhere, and well worth reading. IIRC, the
problem is populating the entire list of supervisor/staff hierarchies.