Access To keep calculation until a condition is met

[mfahadakbar]

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

 

[stumac]

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]

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]

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

 

[james_lankford]

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]

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.

 

[stumac]

Ok here is a stab at it:

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

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.