×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fill array by looping data to find last person

Fill array by looping data to find last person

Fill array by looping data to find last person

(OP)
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

CODE

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.

RE: Fill array by looping data to find last person

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

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 
 
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.

RE: Fill array by looping data to find last person

Hi,

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

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
 

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Fill array by looping data to find last person

(OP)
@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

RE: Fill array by looping data to find last person

Here's simple representative pseudocode

CODE

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

Recursive(p)
  QueryChildrenForParent(p)

  For Each c in p
     Call Recursive(c)
  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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Fill array by looping data to find last person

(OP)
@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?

RE: Fill array by looping data to find last person

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Fill array by looping data to find last person

(OP)
@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

RE: Fill array by looping data to find last person

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...

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  

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Fill array by looping data to find last person

...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Fill array by looping data to find last person

(OP)
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.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close