Temp Table

[JMorrell]

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 creating and
using temp tables?

Basic request: for an employee to look at their sick and annual leave
information; for a supervisor to look at their own employees (and no one
else’s) sick and annual leave information.

Details: I have a "reports" form which has an OnOpen event to capture the
NTUserName, from that I get 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 (or may not)
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.

Here is a bit of my code so far:
' determine if user is supervisor or employee
NTUser = GetNTUserName.fOSUserName()

' get user id number from table
RetUserID = Nz(DLookup("NTUserID", "tblNTUserName", "UserName ='" &
[NTUser] & "'"))

' get user's supervisor ID
RetSuper = Nz(DLookup("SupervisorID", "TblEmp", "EmployeeID ='" &
[RetUserID] & "'"))

' find out if user is a supervisor
SuperFlag = Nz(DLookup("IsSupervisor", "TblEmp", "EmployeeID ='" &
[RetUserID] & "'"))

This code returns the basic information on a single user, but if I want to
drill down to the last employee (if this user is a supervisor) I’m lost.

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 and have supervisors under them (e.g. the
Dept. Director). In this case, the Director should be able to look at the
leave hours of his supervisors and all of their employees.

I really hope this makes sense. Maybe this is too much information. Do I
really need temp tables or is there a better way?

tia