Coding Thread, [SQL] line management of teachers in Coding and Web Development; Hi, I'm trying to work out the best way to do this, either in SQL or VBA...
I have an ...
15th July 2013, 01:16 PM #1
- Rep Power
[SQL] line management of teachers
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!
IDG Tech News
18th July 2013, 06:05 PM #2
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.
19th July 2013, 11:15 AM #3
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
Last edited by jinnantonnixx; 19th July 2013 at 11:16 AM.
By PEO in forum How do you do....it?
Last Post: 8th November 2011, 07:36 AM
By Midget in forum General Chat
Last Post: 12th March 2007, 12:15 PM
By petectid in forum Windows
Last Post: 6th February 2007, 01:05 PM
By Disorder in forum Virtual Learning Platforms
Last Post: 12th January 2007, 04:55 PM
By steve in forum General Chat
Last Post: 13th March 2006, 08:25 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)