Access To keep calculation until a condition is met

mfahadakbar

New Member
Joined
Jun 4, 2018
Messages
6
Hi Guys,

i have a HR table that have people's information in it. It has a column that identifies the Manager of the person. Now , of course , there will be managers of mangers and their mangers and so on , and finally the group head at the top.

Now, is there a way to retrieve the name of the group head of a person , by going up through the hierarchy?

Thanks
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
I cant think of a way to do this without VBA unless you know the number of possible tiers in the hierarchy.

In VBA you could create a function that loops through your records until you get to the desired record - this would need to have a check in it to ensure someone isn't set to be there own manager - or else it would likely get stuck in an infinite loop.

If you aren't sure about writing the code then the following information would help:
Table name
Field Names and types for employee ID and manager ID
Name of fields you want to return (i.e. Employee Name/Job title)
Would you want to show the hierarchy?
What is the criteria to show the person is a group head (they don't have a manager ID above them or they have a specific job title?)
 

mfahadakbar

New Member
Joined
Jun 4, 2018
Messages
6
Hi Stumac,

i knew that it would be something like VBA coding. i have a done a few in Excel, but VBA in Access are new to me.
so your help is much appreciated

Table Name: "RDM_Main"
Filed names: ResourceID , ManagerID (both are text , alphanumeric)
I want to return ResourceID of the top most manager , in a Filed called TLT
Top Most Manager is marked Yes , in field called GroupHead

there is no such record where manager is manger of his own.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
  1. 2013
Platform
  1. Windows
Wouldn't the top manager be the manager without a manager?
Then just look for the people without managers.
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,172

ADVERTISEMENT

Wouldn't the top manager be the manager without a manager?
Then just look for the people without managers.

but what I think he's asking is if you give him the name of the newly hired cashier how can he get the highest ranking manager of that person ?

sort of like .. how would you find the Commander in Chief given the name of a newly enlisted recruit

"Now, is there a way to retrieve the name of the group head of a person , by going up through the hierarchy?"
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
  1. 2013
Platform
  1. Windows
I do understand that but he wants to top of the hierarchy, which he/she seems to define as "the one who has no one above them":

of course , there will be managers of mangers and their mangers and so on , and finally the group head at the top.

Now, is there a way to retrieve the name of the group head of a person , by going up through the hierarchy?

Basically, if everyone who has a manager is not the top, then by set complement we only want the ones who have no managers. If there is still some way of identifying the group (that the on person manages and that the other person belongs to) then job done. If we only need a list of the top managers, then indeed problem solved (in theory, anyway).

However, the question is basically meant to probe the problem. Still, I wouldn't exclude a SQL solution yet.
 
Last edited:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
Ok here is a stab at it:

Code:
Function ResourceGroupHead(resourceID As String) As String
Dim CurrenChk As String
Dim FoundHead As String
Dim rs1 As Recordset
currentchk = EmployeeID
GroupHead = "N/A"
FoundHead = "No"
currentchk = resourceID
Do While FoundHead <> "Yes" And currentchk <> "N/A"
    Set rs1 = CurrentDb.OpenRecordset("Select * from RDM_Main where ResourceID = '" & currentchk & "'")
    If Not (rs1.EOF And rs1.BOF) Then
        rs1.MoveFirst
        If rs1.Fields!GroupHead = "Yes" Then
            FoundHead = "Yes"
        Else
            currentchk = Nz(rs1.Fields!ManagerID, "N/A")
        End If
    Else
            currentchk = "N/A"
    End If
Loop
If currentchk <> resourceID Then ResourceGroupHead = currentchk
End Function

save this into a module and call from query/report/form. e.g.:
Code:
SELECT RDM_Main.*, ResourceGroupHead([ResourceID]) AS TLT
FROM RDM_Main;

I have assumed your GroupHead field is text if its actually a Yes/No data type then this will need to be adapted for that.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,940
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top