Table Design & Linking records.

[Carolb]

This is a three part question.

1st question: I'm creating a database for a pyramid type business. I'm
giving each member a unique id by the auto numbering feature. Each record
will content personal information along with Teir1_member & Tier2_member.
Example:
I start the company, my record would look like.
mem_id First_name Last_name address tier1_member tier2_member
01 Carol my personal info. blank
blank

So. if Joe wants to join under me. His record would look like this:
mem_id First_name Last_name address tier1_member tier2_member
02 Joe Joe'spersonal info. 01(my)mem_id blank

Joe's friend Bill wants to join.
mem_id First_name Last_name address tier1_member tier2_member
03 Bill Bill's personal info. 02(Joe's mem_id)
01(my)mem_id

Each member could have multipule tier1_members and multipule tier2_members.
Would you keep all this information in 1 table? But, as people add you will
have to possibly modify two records. The reason we have tier1_member and
tier2_member is for a payment structure. You get paid on adding members and
if you members add a people. So, it only goes two deep. How would you
advise me to setup the table?

2nd question: This could get big so what the best way to lookup the mem_id
to assign it to tier1_member & tier2_member fields? I'm trying to keep all
the data entry to a minimum.

3rd question: How would you modify records as member join?

Thank you, I know it's a lot and it's confusing. I'm confused myself. I
offer to help a friend and I think I bit off a little more then i can handle.


Carol

 

[KARL DEWEY]

It works out to be simply a matter of relationship which is what Access is
meant to do.
tblMembers ---
mem_id - autonumber
FName - text
LName - text
Addr1 - text
Addr2 - text
City - text
State - text
Zip - text
ZipPlus - text
Phone - text
etc.
SponsorID - number - integer

In the relationship window pull in the tblMembers twice. Access will add a
sufix of '_1' to the table name of the second instance. Click on the mem_id
and drag to the SponsorID of tblMembers_1. Set the relationship to Enforce
Referential Integerity and Cascade Update Related Fields.

Use form/subform to display member/those they sponsored - this is the
down-hill display. You can also have a field in the member query that pulls
their sponsor so as to display the name in the mainform - the up-hill display.