1

I am trying to write an SQL query in Access that will return the relative position of an item/person in a linear network.

For example if we have a table which contains data regarding employees including their first line manager. 

| Employee | Employee ID |  Manager | Manager ID |
--------------------------------------------------
|  John    |     123     |   Craig  |     321    | 
|  Craig   |     321     |   Claire |     874    | 
|  Claire  |     874     |   Ben    |     366    |

I could find an employees 2nd, 3rd... line managers by doing something like this

SELECT t1.Employee, t1.Manager, T2.[Manager] AS [2ndManager], t3.[Manager] As [3rdManager]
FROM ((StaffTable t1 
LEFT JOIN StaffTable t2 ON t1.[Manager ID] = t2.[Employee ID])
LEFT JOIN StaffTable t3 ON t2.[Manager ID] = t2.[Employee ID])

In the example above t1.Employee could refer to someone who is already a 2nd or 3rd line manager. I am unsure how to get an individuals rank e.g. the number of levels there are below them (node height)

Could anyone advise on how best to go about this?

Leroy
  • 634
  • 1
  • 8
  • 33
  • Look into recursion using CTE: https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example – Raj More Jul 27 '17 at 16:07
  • Hi Raj, thanks for the comment. I have looked into this but unfortunately recursive CTEs are not supported by MS Access – Leroy Jul 27 '17 at 16:07
  • Ahh.. the wonderful world of MS Access. This can be achieved using VBA https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access – Raj More Jul 27 '17 at 16:23

0 Answers0