Norbert,
Tell us why this won't work:
SELECT *
FROM [Hierarchy]
WHERE AgentCode = (SELECT Min(AgentCode) FROM [Heirarchy] WHERE AgentStatus = "A" )
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 23 Jul 2015 06:05:37 -0700
Subject: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013
The purpose is to find the Active (A) AgentCode in the following Hierarchy Table:
(Table has about 15,000 records)
AgentCode AgentStatus SupervisorCode SupervisorStatus AgentLevel SupervisorLevel
100 T 200 T 5 7
200 T 300 T 7 13
300 T 400 A 13 19
400 A 400 A 19 19
500 A 600 T 10 14
.................................................................................................................
600 T 800 T 8 12
For example:
First search for AgentCode 100:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
100 T 200 T 5 7
Then if AgentCode is T (Terminated) search for AgentCode 200:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
200 T 300 T 7 13
Then if AgentCode is T (Terminated) search for AgentCode 300:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
300 T 400 A 13 19
Next search for AgentCode 400 and confirm is A (Active) :
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
400 A 400 A 19 19
Agent 100 is T search in the Hierarchy to find out the Active Agent (400 is the response).
Please advise if I should either via VBA or queries.
How do I approach this?
Thanks
Norbert
Tell us why this won't work:
SELECT *
FROM [Hierarchy]
WHERE AgentCode = (SELECT Min(AgentCode) FROM [Heirarchy] WHERE AgentStatus = "A" )
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 23 Jul 2015 06:05:37 -0700
Subject: [MS_AccessPros] Find the Active Agent in a Hierarchy MSAccess 2010/2013
The purpose is to find the Active (A) AgentCode in the following Hierarchy Table:
(Table has about 15,000 records)
AgentCode AgentStatus SupervisorCode SupervisorStatus AgentLevel SupervisorLevel
100 T 200 T 5 7
200 T 300 T 7 13
300 T 400 A 13 19
400 A 400 A 19 19
500 A 600 T 10 14
.................................................................................................................
600 T 800 T 8 12
For example:
First search for AgentCode 100:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
100 T 200 T 5 7
Then if AgentCode is T (Terminated) search for AgentCode 200:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
200 T 300 T 7 13
Then if AgentCode is T (Terminated) search for AgentCode 300:
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
300 T 400 A 13 19
Next search for AgentCode 400 and confirm is A (Active) :
AgentCode AgentStatus SupervisorCode SupervisorStatus Level SupLevel
400 A 400 A 19 19
Agent 100 is T search in the Hierarchy to find out the Active Agent (400 is the response).
Please advise if I should either via VBA or queries.
How do I approach this?
Thanks
Norbert
__._,_.___
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 (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar