SEEKING KNOWLEDGE OF MS ACCESS AND DATABASE

[perfectclick.ng]

I'm a beginner of MS Access, I need adequate knowledge on how to
programme. I have a challenge in my office to write a pragramme that
will calculate a compensation for all our distributors. This is a
network marketing company that has so many distributors, and almost
all the distributors are introduced by one distributo or other.

For example:
Mr. A introduce Mr. B,
Mr. B introduce Mr. C,
And Mr. C introduce Mr. D.
Mr. A has succeeded in having one (one) direct Downline and 2 (two)
indirect downlines.

The Challenge I have is the Hierarchy, calculating the compensation
and reporting how much are we paying all the distributors in a
particular month.

Thanks!

[Allen Browne]

If you are a beginner, the only way you will get this to work is to talk to
others in your organization, find out what software they are using, and buy
it. It would take you at least 2 years of part time tinkering to develop
this in Access.

If you want a starting point, you need all the people in the one table, with
a self-join to the other record for the person who introduced them. For an
introduction to self-joins, see this article:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

Once you have that idea down, you will want to know how to query this.
There's a downloadable example here:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm

For more advanced approaches, search for Celko's material. Here's a starting
point:
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.

<pe*************@gmail.comwrote in message
news:8d**********************************@e4g2000h sg.googlegroups.com...

I'm a beginner of MS Access, I need adequate knowledge on how to
programme. I have a challenge in my office to write a pragramme that
will calculate a compensation for all our distributors. This is a
network marketing company that has so many distributors, and almost
all the distributors are introduced by one distributo or other.

For example:
Mr. A introduce Mr. B,
Mr. B introduce Mr. C,
And Mr. C introduce Mr. D.
Mr. A has succeeded in having one (one) direct Downline and 2 (two)
indirect downlines.

The Challenge I have is the Hierarchy, calculating the compensation
and reporting how much are we paying all the distributors in a
particular month.

Thanks!

[david12]

On Nov 15, 3:29 am, "[e-mail]"
<[e-mail]:

I'm a beginner ofMSAccess, I need adequate knowledge on how to
programme. I have a challenge in my office to write a pragramme that
will calculate a compensation for all our distributors. This is a
network marketing company that has so many distributors, and almost
all the distributors are introduced by one distributo or other.

For example:
Mr. A introduce Mr. B,
Mr. B introduce Mr. C,
And Mr. C introduce Mr. D.
Mr. A has succeeded in having one (one) direct Downline and 2 (two)
indirect downlines.

The Challenge I have is the Hierarchy, calculating the compensation
and reporting how much are we paying all the distributors in a
particular month.

Thanks!

Just found this challenge!
You could start with this 2 table solution:
1. Table of people (tPeople)
Fields: ID (autonumber/primarykey), Name
2. Table of Introductions (tIntros)
Fields: ID (autonum/primarykey), IntroducerID (Long integer
pointing to tPeople's ID), IntroduceeID (same), Date, ynDirect (Yes/
No), Amt
Note: Right in the table design, you can have the drop-down list
of People appear for the 2 foreign keys.
This approach doesn't have the smarts to automatically set the
ynDirect to Yes or No based on examining other records but at least
you can enter it and report on it.
Maybe after a few years of practice you can do the code for that :)
Many ways of calculating the compensation.
You could have an update query fill in the Amt column with
IIf(ynDirect, <RateforYes>, <RateforNo>)
You can have the report calculate the amount in the same way without
using any Amt field (However when it comes to money, it should be set
right away so as to avoid problems with changing rates or rules.)
Or you could make a look up table with the two rates that is related
to tIntros thru ynDirect.

David, Access Application Developer
PS:
I love Lyle's philosophical answer, too.

[david12]

On Nov 18, 12:55 am, david12 <[e-mail]:

On Nov 15, 3:29 am, "[e-mail]"

<[e-mail]:

I'm a beginner ofMSAccess, I need adequate knowledge on how to
programme. I have a challenge in my office to write a pragramme that
will calculate a compensation for all our distributors. This is a
network marketing company that has so many distributors, and almost
all the distributors are introduced by one distributo or other.

For example:
Mr. A introduce Mr. B,
Mr. B introduce Mr. C,
And Mr. C introduce Mr. D.
Mr. A has succeeded in having one (one) direct Downline and 2 (two)
indirect downlines.

The Challenge I have is the Hierarchy, calculating the compensation
and reporting how much are we paying all the distributors in a
particular month.

Thanks!

Just found this challenge!
You could start with this 2 table solution:
1. Table of people (tPeople)
Fields: ID (autonumber/primarykey), Name
2. Table of Introductions (tIntros)
Fields: ID (autonum/primarykey), IntroducerID (Long integer
pointing to tPeople's ID), IntroduceeID (same), Date, ynDirect (Yes/
No), Amt
Note: Right in the table design, you can have the drop-down list
of People appear for the 2 foreign keys.
This approach doesn't have the smarts to automatically set the
ynDirect to Yes or No based on examining other records but at least
you can enter it and report on it.
Maybe after a few years of practice you can do the code for that :)
Many ways of calculating the compensation.
You could have an update query fill in the Amt column with
IIf(ynDirect, <RateforYes>, <RateforNo>)
You can have the report calculate the amount in the same way without
using any Amt field (However when it comes to money, it should be set
right away so as to avoid problems with changing rates or rules.)
Or you could make a look up table with the two rates that is related
to tIntros thru ynDirect.

David,AccessApplication Developer
PS:
I love Lyle's philosophical answer, too.

Actually I should mention that you should use a different name than
"Date".
Maybe it would be dtIntro or IntroDate for date of introduction.

Also wanted to mention that this solution shows something about
letting users to do their share of thinking.
In this case it's real easy for a human to pick whether direct
introduction or not (assuming they have that knowledge handy - if not,
simply list all other introduction records for that introducer for the
user to examine.)
That leaves Access to do what it's good at, namely record keeping. In
this case it can behave much like any compensation application.
Which reminds me, you should have a payment table, too. Or at least a
yes/no field in tIntros table for whether that intro was compensated.
The Payment table would have fields: IntroID (foreign key pointing to
ID of tIntros), PayDate, Amt

Good luck!
David Stelle