Fill array by looping data to find last person

[03Explorer]

I have an ADP data dump that shows all associates in a company. With each associate there are two data points I am focusing on, their UniqueID (or AutoID ADP gives) and a ManagerID, which is the UniqueID of the associate that manages the associate in the row.

If I am a high level manager, my UniqueID is in the rows as manager for my direct reports. I want to build a list of all those below me at all levels, including the bottom person.

So if I am the Senior VP of a company, I want to build a list of all those below me... Directors (their direct reports), Managers (their direct reports), supervisors (their direct reports). With no one below the supervisors not having their ID as a manager, so we hit bottom.

Sample data

UniqueID   LastName     FirstName     ManagerID     Title
========   ========     =========     =========     =====
1          Smith        Frank                       VP_Sales
2          Moore        Bethany       1             Director_Sales
3          Williams     Teddy         1             Director_Marketing
4          Sanderlyn    Gill          2             Manager_Sales
5          Fredrick     Todd          3             Manager_Marketing
6          Bendricks    Tina          3             Manager_TradeShow
7          Potter       April         4             Supervisor
8          Forest       Rose          4             Supervisor
9          Black        Ben           5             Supervisor
10         Twitty       Jenifer       6             Supervisor
11         Smith        Lynn          7             SalesCoordinator
12         Wheeler      Tammy         8             SalesAdminAssistant
13         Getty        Jeffery       9             MarketingCoordinator
14         Silver       Hank          10            MarketingTradeShowAdminAssistant

So if Frank Smith is logged into the system, Frank being EmployeeID (UniqueID) of 1, His list of people below him would include Everyone in the list above. However if Tina Bendricks(6) signs in, her list would be Jenifer Twitty and Hank Silver. But if Bethany Moore(2) signs in, her list would include Gill Sanderlyn, April Potter, Rose Forest, Lynn Smith and Tammy Wheeler.

I am not certain if I use VBA to loop through the ADP list or rather use SQL (Either TSQL or Access's SQL). My gut is telling me to use TSQL or Stored Procedure, but thought I'd start here in VBA land for questioning.

So the goal is to have a list of (not including self) but of all associates First and Last names so I can use them in a drop down for selecting.

Thanks for insight or conceptual ideas to help make this happen.

 

[Andrzejek]

if [red]Bethany Moore(2)[/red] signs in, her list would include [blue]Gill Sanderlyn, April Potter, Rose Forest, Lynn Smith[/blue] and [blue]Tammy Wheeler[/blue]."

[pre]
UniqueID LastName FirstName ManagerID Title
======== ======== ========= ========= =====
1 Smith Frank VP_Sales[red]
2 Moore Bethany 1 Director_Sales[/red]
3 Williams Teddy 1 Director_Marketing[blue]
4 Sanderlyn Gill 2 Manager_Sales[/blue]
5 Fredrick Todd 3 Manager_Marketing
6 Bendricks Tina 3 Manager_TradeShow[blue]
7 Potter April 4 Supervisor
8 Forest Rose 4 Supervisor[/blue]
9 Black Ben 5 Supervisor
10 Twitty Jenifer 6 Supervisor[blue]
11 Smith Lynn 7 SalesCoordinator
12 Wheeler Tammy 8 SalesAdminAssistant[/blue]
13 Getty Jeffery 9 MarketingCoordinator
14 Silver Hank 10 MarketingTradeShowAdminAssistant
[/pre]
Any reason / logic why Williams, Fredrick, Bendricks, Black, Twitty, Getty, and Silver are NOT on Bethany Moore's list?

I can understand Williams will be excluded as another 'Director (1)'...

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Hi,

This is like a typical multi-level bill-of-material that at its simplest would look like this...
[pre]

ManagerID EmpID
1 2
1 3
2 4
3 5
3 6
4 7
4 8
5 9
6 10
7 11
8 12
9 13
10 14
[/pre]

I've always done this with a recursive procedure, a procedure that calls itself given a Child as a Parent.

So if you Supply the Procedure with Parent 5, it queries to get Children 9.
So if you Supply the Procedure with Parent 9, it queries to get Children 13.
So if you Supply the Procedure with Parent 13, it queries to get Children----

No children at the bottom. So the recursive pops back to the next level up etc.

Thats how you generate a multi-level list.

If we had started with Parent 4, the query would have returned Children 7&8, so 7 woul return 11 and 11 has no Children etc, etc...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[03Explorer]

@Andy
The reason if (2) signs in and can't see Williams, Fredrick, Bendricks, Black, Twitty, Getty, and Silver is as a manager Beth is only able to see her data and anyone who is under her, not even her peers data (or the peer's subordinates).

@Skip
I need to soak up the flip you threw at me. I believe it is what I need, but I have to wrap my mind around the change.

BTW, the concept I am deriving is Who-Can-I-See... it's directly tied into HR functions, so that's why it only includes the person who signed in and anyone who is below them in the organization based on the ADP data imports. It's a way of security happening automatically when HR changes who reports to who or when new staff come into the org. Also for CEO to see all below him/her without anything special.

Thanks guys, I am chewing on this as a project for the week.

Cheers!!
Rob

 

[SkipVought]

Here's simple representative pseudocode

Main
'Supply a ParentID to Recursive
  Call Recursive(pid)
End

[b]Recursive(p)[/b]
  QueryChildrenForParent(p)

  For Each c in p
     Call [b]Recursive(c)[/b]
  Next
End

BTW, who supplies the table you posted?

I had a work situation where my direct report manager was not the person who directed my work efforts. So I had a direct report for administrative purposes and a functional report for productive purposes. Such multiples should appear in such a table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[03Explorer]

@Skip

The table provided is by our HR personnel coordinator who receives a data dump from Finance. HR person takes the RAW file and adjusts it to meet business details to then hand it over to my group to import into the system.

Am I wrong in thinking maybe this should be generated in TSQL vs manipulate VBA? Would the method of using VBA be a bit more labor or processor intensive vs doing either TSQL query or a SPROC?

Would it be allowed for me to post this question in SQL subject as well as here? (Not wanting to step on toes by having same concept being worked on by both sides VBA / TSQL(SQL). No?

 

[SkipVought]

I may be wrong, but due to the fact that an organization can have a varying number of levels, unless a query view is constructed to drill down to the maximum level ever expected, a recursive routine is the answer.

Someone might know how to construct such a view.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[03Explorer]

@Skip

Thanks, you are right... there could be any number of levels and a recursive process is going to be the ticket.

Hoping that someone has a direction advise for me... VBA or SQL (TSQL)

Thanks,
Rob

 

[SkipVought]

If you decide to go the recursive route, I'd be glad to assist. It's probably been over a decade and I've been retired 5 years. As I recall some of the BOM displays I've done included an indent for each level to visually display the organization/assembly level. So the drill-down display might look something like this for Frank Smith...

[pre]
1 Smith Frank VP_Sales
2 Moore Bethany 1 Director_Sales
4 Sanderlyn Gill 2 Manager_Sales
7 Potter April 4 Supervisor
11 Smith Lynn 7 SalesCoordinator
8 Forest Rose 4 Supervisor
12 Wheeler Tammy 8 SalesAdminAssistant
3 Williams Teddy 1 Director_Marketing
5 Fredrick Todd 3 Manager_Marketing
9 Black Ben 5 Supervisor
13 Getty Jeffery 9 MarketingCoordinator
6 Bendricks Tina 3 Manager_TradeShow
10 Twitty Jenifer 6 Supervisor
14 Silver Hank 10 MarketingTradeShowAdminAssistant [/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...and although not as necessary, perhaps, as in a BOM, there can be a WhereUsed list which could tell you what organizations an employee is attached to if that sort of thing is part of your company's structure.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[03Explorer]

I believe I am very close to getting the results I have been seeking. I've been digging and it appears SQL is going to be the method I find to work best. It's SQL I am passing to the SQL server (Back end) so I am able to leverage more powerful options ie With or CTE (Common Table Expression). I'll post my code here but feel I need to move the discussion to a SQL path to finish my objective. The code below provides all levels (recursive) from seed associate down starting at their first level, then their first level direct reports... their direct reports and so on until there is no more associates who have direct reports. My last hitch is that I need the list to include the person them self in the list as level 0.

WITH DirectReports(ManagerID, EmployeeID, Associate, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, AssociateID, LegalLastName + ', ' + LegalFirstName AS AssociateName, Title, 0 AS EmployeeLevel  
    FROM AssociateMaster 
    WHERE 
      managerID = 2  --Bethany

    UNION ALL  

    SELECT e.ManagerID, e.AssociateID, e.LegalLastName + ', ' + e.LegalFirstName, e.Title, EmployeeLevel + 1  
    FROM dbo.AssociateMaster AS e  
      INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   

)  
SELECT DR.ManagerID, MGR.LegalLastName + ', ' + MGR.LegalFirstName as Manager, DR.EmployeeID, DR.Associate, DR.Title, DR.EmployeeLevel   
FROM DirectReports DR
  INNER JOIN AssociateMaster as Mgr ON DR.ManagerID = Mgr.AssociateID
ORDER BY 
  DR.EmployeeLevel Asc, 
  MGR.LegalLastName + ', ' + MGR.LegalFirstName ASC,
  DR.Associate Asc

option (maxrecursion 0);

Using VBA seems to be more overhead. (I could be wrong and posted this in hopes someone can either align with my direction or redirect me based on their experience)

Link to the SQL thread I started: TSQL CTE

Thanks!
Rob