How Do I Do This

[hendafe]

I am relatively new to Access Databases. I have a database my boss
wants me to create. We are into network marketing, known as
FineNess. I have figured out the necessary tabes to be able to track
purchases, sales and compensation calculation, inventory.

The problem I have is on how to automatically group members based on
who Registers them. Example,

If Mr. A is a member, and he registers Mr. B, then Mr. B is said to be
in the First Generation of Mr. A.

Subsequently, if Mr. B. introduces Mr. C, then Mr. C. is of the Second
Generation of Mr. A, and First Generation of Mr. B.

Initially I thought I could have a table Called
M_tblRegistrationDetails, with fields like RegistrationID,
RegistrationDate, RegisteredBy, RegGeneration, etc. This will help me
create a query that will group each member by Generation and
subsequently calculate their compensation.

The problem with this is that it requires a lot of manual entry, and
error could be high.

Does anyone have a suggestion. I will be grateful. I am under
serious pressure

[Allen Browne]

Use a self-join on the table to record who is the immediate up-line of the
member.

It works like a pedigree, where a the previous generation is the "parent".
Here's an example:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

It is also similar to assemblies that have subassemblies. Here's a sample
database that demonstrates that:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm

The design is simple to implement, and simple to interface. If you have to
trace all the nodes up-line, it gets more complex. Here's some reading from
Joe Celko if you need to do that:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<he*****@gmail.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.com...

>I am relatively new to Access Databases. I have a database my boss
wants me to create. We are into network marketing, known as
FineNess. I have figured out the necessary tabes to be able to track
purchases, sales and compensation calculation, inventory.

The problem I have is on how to automatically group members based on
who Registers them. Example,

If Mr. A is a member, and he registers Mr. B, then Mr. B is said to be
in the First Generation of Mr. A.

Subsequently, if Mr. B. introduces Mr. C, then Mr. C. is of the Second
Generation of Mr. A, and First Generation of Mr. B.

Initially I thought I could have a table Called
M_tblRegistrationDetails, with fields like RegistrationID,
RegistrationDate, RegisteredBy, RegGeneration, etc. This will help me
create a query that will group each member by Generation and
subsequently calculate their compensation.

The problem with this is that it requires a lot of manual entry, and
error could be high.

Does anyone have a suggestion. I will be grateful. I am under
serious pressure