Circular reference?

[Titlepusher]

I want to design a database which a Multi Level Marketing company would use.
Standard MLM format. Example: Member A recruits Members A1, A2 and A3.
Member A2 recruits Members A2A, A2B and A2C Member A2C recruits Members
A2Ca, A2Cb.... etc.

I anticipate setting it up so that each Member record in the Member table
will have to be linked to a Member who recruited them... I assume that
there should there be a second table which just links Member ID's... a
one-to-many.

My question is very general... Once I have the two tables... (master and
cross ref).. How to I query to show the entire tree of members (sub-members,
sub-sub members, etc..) under each member.. they can be 20 - 50 deep.

thanks,

tp

 

[Ray]

You need to have a table that contains the keys of the recruiter and
recruited members. One approach is to create a function that calls itself.
Pass the key of the recruiter and read the members that he/she recruited.
For each recruited member, save it (or do whatever you need to do) and then
call yourself with the new linked member.

Caution, you need to keep track of the current chain of linked members to
prevent an infinite number of recruisive calls. Save the chain of
member/record IDs in a globel array and check the list on entry to the
routine to see if the passed key is already in the list.

 

[John W. Vinson]

I want to design a database which a Multi Level Marketing company would use.
Standard MLM format. Example: Member A recruits Members A1, A2 and A3.
Member A2 recruits Members A2A, A2B and A2C Member A2C recruits Members
A2Ca, A2Cb.... etc.

I anticipate setting it up so that each Member record in the Member table
will have to be linked to a Member who recruited them... I assume that
there should there be a second table which just links Member ID's... a
one-to-many.

My question is very general... Once I have the two tables... (master and
cross ref).. How to I query to show the entire tree of members (sub-members,
sub-sub members, etc..) under each member.. they can be 20 - 50 deep.

thanks,

tp

You can do this all with one table, with self-joins. The primary key of the
table would be the MemberID; there'd be a field RecruitedBy of the same
datatype (Long Integer if the MemberID is an autonumber), containing th ID of
the recruiting member.

You would need some VBA code in the BeforeUpdate event of the query to "crawl
the tree" looking for circularities.

 

[KARL DEWEY]

I think you need what is known as a 'Self-join'. An example would be an
employee list that included a Foreign Key field for Supervisor. Supervisors
would themselves be employees and be in a one-to-many relationship with
employees.
In the Relationship window add the table twice. Access adds a sufix of '_1'
to the table name of the second object.
Click on the Primay Key field of first table and drag to the Foreign Key
field of the second table. Select Referential Integerity and Cascade Updates.