I've in the past seen a helpful article on how to take an employee/ supervisor table to create an employee hierarchy.

It would start with an employee lookup on their supervisor and so on until it reached the top. I believe that it was done by simply adding the same table in the query multiple times to get this relationship.

Has anyone seen this before?
The example I saw used the Nortwind database as an example

Thanks,
vetter

Vetter,

I'm not sure what you want to do here. But you can use the
Search Facility here to look for "Cascading Combos". You want
a dynamic method of traversing your data.

Wayne

Vetter, what you are describing is the classic example of a table that needs to be related to itself in order to create a hierarchy.

In my own databases with employee tables, I have an employeeID and I have a reportsToID. Like this:

EmpID
EmpName
ReportsToID

You can then add the table to a query multiple times in order to display a user hierarcy.

This post touches on the topic a bit: Applying Recursive Relationships to Tables (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=9651).

As an aside this recursive relationship can appear in a number of guises

Supervisor Employee
Assembly Component
Parent Child

Can be very useful but I have not yet found a way of making it self intelligent regarding how many levels exist. Usually assessed situation to determine likely number of levels required and then assumed that double this quantity may occur in practice.

len

I don't have any examples handy but if you search for bill of material or BOM, you should find posts that point to sample databases you can download. I haven't found a way to do this with SQL either without fixing the number of levels but I have done it with code (in COBOL) in a former life.