[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: drnorbert@msn.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___