Query on results of query

J

JMorrell

A super challenge... at least for me.

I have a form where a user (either a supervisor or an employee) can access
his or her Annual and Sick leave report (which shows balances, usage, etc).
I've got it set so that if the NT User (the logged on userID) is a
supervisor, I can capture their immediate employees and their employees’
reports. If any of these immediate employees are themselves supervisors, I
want to be able to run a query to bring up their data.

A real example: I have a Director (Bill), who has 4 supervisors (Lou, Rick,
Kathy, and Joe). Let's say that when the Bill logs on to his pc and opens
this reporting form, I want him to be able to look at the leave records for
himself, Lou, Rick, Kathy, and Joe as well as all the supervisors and/or
employees that report to Lou, Rick, Kathy, or Joe.

In addition, if Kathy logs on to her pc and opens the reporting form, I want
her to be able to look at her own leave records, and any supervisor and/or
employee that report to her or any of her supervisors.

The higher up in the hierarchy, the more leave reports they should see. Me,
being a mere employee, would be able to see just my records, and no one
else's. There can be any number of employees downstream, so to speak; but
there is a finite number of supervisors (thank goodness for that).

I have it working now that when Bill opens the report he can get the leave
records for Lou, Rick, Kathy, and Joe; but not anyone downstream of them.
I’m thinking I could run a query based on the results of another query. Each
employee has empID, IsSupervisor (yes/no), SupervisorID fields in their
record.

Any help in this endeavor is very much appreciated.
JMorrell
 
P

Peter Martin

HI,

There are two ways to do this: 'Nested sets' and 'association matrix'.

You're probably using 'association matrix'. You have a table of users and
who they report to. If you're using MSDE or sql server, get/look in SQL
server "books on line" search for topic 'Transact-SQL Tips' 'expanding
heirachies'.
If you're using Jet, there is equivalent on the web in code. Problem is,
this pushes all the processing to the read end - you can't just run a query
in jet to get the list you want. The processing time may be unacceptable to
the execs in your op.

If your personell data is semi-static (ie not too many new employees every
week!), then you could add a couple numeric fields to your employee table and
use a 'nested set'. With this, there is a simple query for forms/reports to
get what you want. This transfers the work from the 'read' end to the
'write' end - you run code when you're adding/moving employees but just a
query to read. The code is a recursive-descent parser that maps out the
structure to the two fields you add. Look for 'Celko nested', 'nested bom'
on the web to get the code you need. (bom=bill of materials)

HTH,

Peter
 
J

JMorrell

Thanks for the reply.

I've been looking at Celko's musings on the subject and got lost with the
lft and rgt columns in his adjacency list model. I don't understand the
logic of node numbering. I'll look at the SQL online book area for
additional information.

In my situation, we Do have a slight revolving door when it comes to new
employees (sad to say).

I even thought of loading temp tables to hold all downstream employee data,
then clearing the tables after the report was written.

Can someone shed some light on the node logic to me?

thanks again,
JMorrell
 
Top