Affiliate Membership tracking

[Guest]

I am part of a group with 60,000 members and growing. Several hundred have
expressed a desire to all join an affiliate program, and it is important that
I be able to organize and track the joining process, so that each member gets
2 other members to join under them. I would like to use Access to do all of
this tracking, but cant seem to get the correct table design to allow me to
accomplish this in an expeditious manner. I would then like to be able to
show links between all members, hopefully graphically. I have each member in
an overall database, with membership user id, email address, and other info
relating to their initial membership. Can someone suggest a table structure
to allow me to start with 6 front line members, have 12 join under those 6,
then 24 under the 12, and so on? I can provide more specific data if needed,
but I need an answer as rapidly as possible please. I would like to give
immediate directions to the 12, to start joining under the first 6, and then
let me know when that is accomplished. I also am not sure if I need to track
the 'affiliate pending" status from pending to completion. I dont think I
need to, but I will want to run a query on all of the necessary tables to see
who does not yet have their 2 affiliates. I do know how to structure that
query, by just including the Aff1 and Aff2 fields, and then placing an IS
null in the Criteria fields, and arranging them in an "OR" logic fashion.
What I can't seem to get is table relationships defined. Thanks for any help.

 

[Allen Browne]

The simplest approach would be to add another field to your Member table, to
hold the MemberID value of the person who convinced them to join.

The Member table would have fields like this:
MemberID AutoNumber primary key
Surname Text
FirstName Text
IntroByMemberID Number
You then add a 2nd copy of this table to the Relationships window (Tools
menu.) Access aliases it as Member_1. Then you can drag Member_1.MemberID
onto Member.IntroByMemberID to create the relationship.

For your existing members (not introduced by another), the IntroByMemberID
field is Null (blank.)

For another example of a joining a table to itself in this way, see:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

 

[Guest]

Thank you very much. A perfect and simple solution!

 

[Guest]

sniff, sniff...
I smell pyramid marketing scam.

 

[Guest]

Leave it to someone to be cynical. Actually, no one is making any money on
this, as it does not pay down multiple levels, it is just so that our
membership receives the use of several very effective marketing tools at no
cost.

 

[Guest]

Okay, whatever