Considering this is Acces queries I'm not sure there is a more efficient solution. With SQL Server, you can use recursion (at least people smarter than me can).
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 24 Jul 2015 18:13:36 -0700
Subject: RE: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013
Duane,
Your approach works fine for this example, I was sending a simple sample however in reality is more complicated. I created a function which is working fine ( see below).
Please let me know if you can suggest an easier way via SQL.
Public Function GetActiveAgent(strAgent As String) As String
Dim sResult As String
Dim strAgentCode As String
Dim strAgentStatus As String
Dim strSupCode As String
Dim dbsDB As Database
Dim rstRS As Recordset
Dim strSQL As String
Set dbsDB = CurrentDb
strAgentCode = strAgent
Do Until Len(sResult) > 0
strSQL = "SELECT * FROM QS_AgentActive WHERE AgentCode='" & strAgentCode & "'"
Set rstRS = dbsDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Debug.Print strAgentCode & "-" & rstRS!AgentName & "- Agent Status=" & rstRS!StatusOfAgent & " - Contract Status= " & rstRS!StatusOfContract & " - email: " & rstRS!EMailAddr
If rstRS!StatusOfAgent = "A" Then
sResult = strAgentCode
Else
strAgentCode = rstRS!SupervisorAgentCode
If rstRS!AgentCode = rstRS!SupervisorAgentCode Then
Exit Do
End If
End If
Loop
GetActiveAgent = sResult
End Function
Function call from inmediate window:
getActiveAgent("904909")
904909-Agent Status=T - Contract Status= T
904722- Agent Status=T - Contract Status= T
904772- Agent Status=T - Contract Status= T
902170- Agent Status=T - Contract Status= T
900001- Agent Status=T - Contract Status= T
903481- Agent Status=A - Contract Status= T
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 24 Jul 2015 18:13:36 -0700
Subject: RE: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013
Duane,
Your approach works fine for this example, I was sending a simple sample however in reality is more complicated. I created a function which is working fine ( see below).
Please let me know if you can suggest an easier way via SQL.
Public Function GetActiveAgent(strAgent As String) As String
Dim sResult As String
Dim strAgentCode As String
Dim strAgentStatus As String
Dim strSupCode As String
Dim dbsDB As Database
Dim rstRS As Recordset
Dim strSQL As String
Set dbsDB = CurrentDb
strAgentCode = strAgent
Do Until Len(sResult) > 0
strSQL = "SELECT * FROM QS_AgentActive WHERE AgentCode='" & strAgentCode & "'"
Set rstRS = dbsDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Debug.Print strAgentCode & "-" & rstRS!AgentName & "- Agent Status=" & rstRS!StatusOfAgent & " - Contract Status= " & rstRS!StatusOfContract & " - email: " & rstRS!EMailAddr
If rstRS!StatusOfAgent = "A" Then
sResult = strAgentCode
Else
strAgentCode = rstRS!SupervisorAgentCode
If rstRS!AgentCode = rstRS!SupervisorAgentCode Then
Exit Do
End If
End If
Loop
GetActiveAgent = sResult
End Function
Function call from inmediate window:
getActiveAgent("904909")
904909-Agent Status=T - Contract Status= T
904722- Agent Status=T - Contract Status= T
904772- Agent Status=T - Contract Status= T
902170- Agent Status=T - Contract Status= T
900001- Agent Status=T - Contract Status= T
903481- Agent Status=A - Contract Status= T
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar