[SQL] line management of teachers

[gavcradd]

Hi, I'm trying to work out the best way to do this, either in SQL or VBA...

 

I have an Access database table with all of the staff in the school (containing fields USERNAME, JOBTITLE and LINEMANAGEDBY). The "linemanagedby" field is a reference to the username field. So for example, I can easily find out who line manages the head of ICT or all of the people that an assistant head line manages. This works perfectly.

 

Now onto the problem...I've been asked to extend the system so that I can find out "greater depths" of line management. So for example, the assistant head line manages the head of ICT, the head of ICT line manages the ICT teachers and one of the ICT teachers line manages an NQT in the department. I need the system to be able to search for the Assistant Head and return all of the people that they directly line manage, and all of the people that they "indirectly" line manage if you understand me.

 

I've started SQL queries and given up. I started to write a recursive function in VBA but gave up when it got stupidly complicated...

 

Any ideas? Thanks!

[pcstru]

Oracle used to have a nice little SQL clause "Connect by" that would traverse tree structured data. Here's a post apparently doing the same in MsSQL.

[jinnantonnixx]

Pinal Dave's blog is good, and this example is pretty close to what you're looking for:

 

SQL SERVER – Simple Example of Recursive CTE | Journey to SQL Authority with Pinal Dave

Edited July 19, 2013 by jinnantonnixx