Hi Nelly,

If I understand correctly, you want to describe a hierachy of agents and want to be able to report the hierarchie on demand.

If you add a "recruited_by" field to the Agent table and store the ID of the agent which recruited this agent in it, you've got a recursive mechanism in your table.

when you want to show the hierarchy you can write a routine which shows the hierarchy.

FUNCTION CreateHierarchy 
LPARAMETER nParentAgentId, nLevel

nLevel=IIF(EMPTY(nLevel),0,nLevel)

SELECT Agent
SCAN FOR RecuitedBy = nParentAgentId
     ? SPACE(nLevel*3), AgentId, Agent.Name
     nRecno = RECNO()
     DO CreateHierarchy WITH AgentID, nLevel+1
     GO nRecno
ENDSCAN

Because this routine is recursively called you're limited to about 128 levels deep (VFP Do level limitation). If you want more levels, you might want to adjust the routine.

Good luck,

Walter,

>Hi
>
>I've been asked to design a database. Im having trouble with it though, it sounded easy at first but i just can't get my head around it.
>
>I need to store all AGENTS details which is the easy bit, but each agent is known as AGENT(A), these agents then recruit 4 people, known as AGENT(B), agent(b) recruit 4 people each known as AGENT(C) ect.... (but each agent is also an agent(a) as I need to monitor all agents progress to layer (D) before agents(a) work is completed. The database must be able to list all connected agents on request.
>
>Hope you understand, any help would be great!
>
>Cheers
>Nelly