Expert Help Needed: Hierarchies

[foxhound]

Hello,

With great frustration, I have searched this board and the web but couldn’t get a definite answer. I have a table with employee numbers, employee names, supervisor numbers, and supervisor names. When I select a supervisor by using a query or recordset in VBA, I need to have code or query to create a list of subordinates for that supervisor, their subordinates, their subordinate’s subordinates, etc…until there are no more levels.

I am desperate need! How can this be accomplished using Access 2000?


Foxhound

 

[Andrew Fergus]

Hi foxhound

It is with some trepidation that I am answering this question. Whilst I have seen this technique used on other platforms I have never tried it on Access myself (until now) and I'm not sure of the level of support for such a technique. From my understanding of your problem you are trying to create a structure that will support an organisation chart, or something similar. In that structure each employee reports to a supervisor, who in turn has a superior and so on.

In it's most basic form, your employee table has (for examples sake) 3 fields being: a unique identifier (the key); a name and a supervisor id (same field type as the key, indexed with duplicates ok, not required).

In your relationship screen (Tools -> Relationships), add the employee table twice. Link the supervisor field from the original table to the employee id field of the second table (it should have a "_1" suffix), enforce referential integrity and tick the cascade update field. Now you have a structure where the employees and supervisors are entered into the same table and you can have many many hierarchies.

Whilst this will give you a data structure that might fit your problem, I don't know how much you can do with it (given I haven't used one in Access). Other people should be able to help with cascading combo boxes (which is what I think you want) - they are not my forte.

HTH, Andrew

 

[foxhound]

Hi Andrew,

Thanks for taking a look at this. I did as you noted but am unclear what step I need to take next. How do I access the records in "mytable_1"?

 

[Andrew Fergus]

The table with the "_1" suffix only exists on the relationship screen (and possibly the query design screen) and is a copy of the original table. As such it should not appear in your list of tables and there is no data to access in that table because the data already exists in the original table.

To input new data into this structure, create a new query based on the original employees table, add all of the fields from the employee table and build a form based on the query (I just use the form wizard for the purposes of testing "proof of concept"), remove the "supervisor" field and replace it with a combo box that looks up the employees and stores the employee id in the supervisor field. This will give you a form that allows you to maintain the employee / supervisor records.

We can discuss the reports once you have got the form working.

HTH, Andrew