Hierarchical Queries

[milkman2500]

Hi,

I'm trying to find the best way to handle hierarchical relationships for employees in an access database.

I currently have a table that simply holds each employee ID and their manager ID. I've then created a query using this method:

http://msdn.microsoft.com/en-us/library/office/aa188216(v=office.10).aspx

The problem is the company I've created this for has thousands of employees and around 10 layers (which requires 10 copies of the table in the query). I need the hierarchy for other reports and have included this query in other queries. I'm finding it slowing things down and may not be the best approach for this hierarchy.

Can anyone recommend an alternative they use?

Would it be better to maintain all 10 layers of management in the original table? So for example:

employee_Id
manager1_id
manager2_id
manager3_id
etc...

This won't be as dynamic but might be faster to use in other queries/reports. What do you think?

 

[CJ_London]

This is a common problem. What you have is what is called an adjacency data set. SQL is not very good at managing hierarchy as you have found - so if another layer is added, you need to add another query.

However there is a solution called Nested Sets which enables you to structure your data so the data can be interogated easily - the algorithm is very similar to what Access and other db's use to create and manage indexes (I used them many years ago for indexing text files). And once built easy to maintain.

This link should get you started - it is for mySQL, but a VBA equivalent can be built

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

At the bottom, you will find a link to an oracle solution for converting an Adjacency set to a Nested set.

Note that Nested sets rely on child records only having one parent (but one parent can have many children), so if you need to also have 'dotted line' reporting it will require further development.

You will also need just one record with a null value parent (presumably your chairman?)