Tiered Commission Report

[Jeff @ CI]

Access 2000 database.

With Allen Browne's help, I now have a database that will track and display
a 3 tiered downline of client's with recruits. HUGE THANK YOU Allen!!!!!!!

http://www.microsoft.com/office/com...da77&mid=64a67165-0b75-450c-a776-4b2559ef78eb



I have now been trying to build a form/report that builds from there. The
criteria is:

- Date Range selectable [ContractDate] in tblClient and [DatePaid] in
tblMonthlyFees
- all clients need to be [Active] = True
- all clients must be in downline of Client being paid
- Commission is paid in three levels corresponding to the SaleLevel of the
Client being paid.
- commission includes the price subclients paid for tuition and their
monthly fees
- if Client being paid sold a tuition package to a new client and needed
assistance, commission for that subclient is cut in half (have a Y/N field to
flag these sales).

My attempts have thus far included trying to build a new query to include
all fields
( [ClientID], [UpLevelID], [ContractDate], [DatePaid] (for monthly fees),
[Assist], [SaleLevel], [SaleAmount], and [MonthlyFee] along with the downline
query fields of [TierLevel1], [TierLevel2], and [TierLevel3] (all have the
client's ID # and name concatonated) ), adding fields to the downline query,
and creating a query to build a form to link the contract info (clientID,
date, amount, fees, etc.) to the downline form. (I get a #Name? error).

Once I am able to display the information for the new sales of a client's
downline along with currently paid fees (the reason for date filtering) - I
will need to total up all monies owed. My current downline form has three
subforms to display each tier of the downline.

I also have a static table with the commission structure to reference to (3
rows coorelate to the 3 tiers, 6 columns to relate to the SaleLevel) in
calculating the total amount.

All this because I did a simple Access database and the CEO thought I was a
genius! Crap on me!!!

Anyhow, any help on getting this monster put to bed (after this form/report,
I am creating reports that I already know how to do) is HUGELY APPRECIATED!!!

Many thanks in advance,

Jeff

 

[Allen Browne]

Jeff, others may be able to contribute to this, but my guess is that this is
too big an issue to achieve with newsgroup postings. (And no: I'm not asking
to give you commercial support, as I don't have time to do that at present.)

There are several involved issues here, such as producing a *tracable*
structure of what commission payments resulted from what sales (and
therefore knowing which sales have not had commissions paid yet), locking
sales records once commissions have been paid, the possibility of handling
changes in the commission rates at some time in the future (so the
percentage is up to x% until 1/1/2009 and then y%, for each level, tier, and
download), handling reversals (for entries paid incorrectly), etc, etc.

The crucial aspect is that you must design something that not only works
correctly, but can be demonstrated to be working correctly. That's why
'tracable' is so crucial here.

Presumably you have tried to investigate what software is being used in
other places for the organization you work with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2000 database.

With Allen Browne's help, I now have a database that will track and
display
a 3 tiered downline of client's with recruits. HUGE THANK YOU
Allen!!!!!!!

http://www.microsoft.com/office/com...da77&mid=64a67165-0b75-450c-a776-4b2559ef78eb



I have now been trying to build a form/report that builds from there. The
criteria is:

- Date Range selectable [ContractDate] in tblClient and [DatePaid] in
tblMonthlyFees
- all clients need to be [Active] = True
- all clients must be in downline of Client being paid
- Commission is paid in three levels corresponding to the SaleLevel of the
Client being paid.
- commission includes the price subclients paid for tuition and their
monthly fees
- if Client being paid sold a tuition package to a new client and needed
assistance, commission for that subclient is cut in half (have a Y/N field
to
flag these sales).

My attempts have thus far included trying to build a new query to include
all fields
( [ClientID], [UpLevelID], [ContractDate], [DatePaid] (for monthly fees),
[Assist], [SaleLevel], [SaleAmount], and [MonthlyFee] along with the
downline
query fields of [TierLevel1], [TierLevel2], and [TierLevel3] (all have the
client's ID # and name concatonated) ), adding fields to the downline
query,
and creating a query to build a form to link the contract info (clientID,
date, amount, fees, etc.) to the downline form. (I get a #Name? error).

Once I am able to display the information for the new sales of a client's
downline along with currently paid fees (the reason for date filtering) -
I
will need to total up all monies owed. My current downline form has three
subforms to display each tier of the downline.

I also have a static table with the commission structure to reference to
(3
rows coorelate to the 3 tiers, 6 columns to relate to the SaleLevel) in
calculating the total amount.

All this because I did a simple Access database and the CEO thought I was
a
genius! Crap on me!!!

Anyhow, any help on getting this monster put to bed (after this
form/report,
I am creating reports that I already know how to do) is HUGELY
APPRECIATED!!!

Many thanks in advance,

Jeff

 

[Jeff @ CI]

Allen, you bring to light some issues that I hadn't thought about and again
bring value to my problem and to all who read this board.

As I pointed out at the end of my original post, my mistake was creating on
my own a database which was fairly simple. The boss thinks I can do this and
sees this as simple too.

We are Office 2000 based at work. I am guessing that the boss doesn't want
to put out X-grand in cash if he thinks I make something work.

The solution seems just out of reach - that of creating a subform with the
"money" info linked to the downline forms I was able to create.

thanks for taking a look

Jeff

Jeff, others may be able to contribute to this, but my guess is that this is
too big an issue to achieve with newsgroup postings. (And no: I'm not asking
to give you commercial support, as I don't have time to do that at present.)

There are several involved issues here, such as producing a *tracable*
structure of what commission payments resulted from what sales (and
therefore knowing which sales have not had commissions paid yet), locking
sales records once commissions have been paid, the possibility of handling
changes in the commission rates at some time in the future (so the
percentage is up to x% until 1/1/2009 and then y%, for each level, tier, and
download), handling reversals (for entries paid incorrectly), etc, etc.

The crucial aspect is that you must design something that not only works
correctly, but can be demonstrated to be working correctly. That's why
'tracable' is so crucial here.

Presumably you have tried to investigate what software is being used in
other places for the organization you work with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2000 database.

With Allen Browne's help, I now have a database that will track and
display
a 3 tiered downline of client's with recruits. HUGE THANK YOU
Allen!!!!!!!

http://www.microsoft.com/office/com...da77&mid=64a67165-0b75-450c-a776-4b2559ef78eb



I have now been trying to build a form/report that builds from there. The
criteria is:

- Date Range selectable [ContractDate] in tblClient and [DatePaid] in
tblMonthlyFees
- all clients need to be [Active] = True
- all clients must be in downline of Client being paid
- Commission is paid in three levels corresponding to the SaleLevel of the
Client being paid.
- commission includes the price subclients paid for tuition and their
monthly fees
- if Client being paid sold a tuition package to a new client and needed
assistance, commission for that subclient is cut in half (have a Y/N field
to
flag these sales).

My attempts have thus far included trying to build a new query to include
all fields
( [ClientID], [UpLevelID], [ContractDate], [DatePaid] (for monthly fees),
[Assist], [SaleLevel], [SaleAmount], and [MonthlyFee] along with the
downline
query fields of [TierLevel1], [TierLevel2], and [TierLevel3] (all have the
client's ID # and name concatonated) ), adding fields to the downline
query,
and creating a query to build a form to link the contract info (clientID,
date, amount, fees, etc.) to the downline form. (I get a #Name? error).

Once I am able to display the information for the new sales of a client's
downline along with currently paid fees (the reason for date filtering) -
I
will need to total up all monies owed. My current downline form has three
subforms to display each tier of the downline.

I also have a static table with the commission structure to reference to
(3
rows coorelate to the 3 tiers, 6 columns to relate to the SaleLevel) in
calculating the total amount.

All this because I did a simple Access database and the CEO thought I was
a
genius! Crap on me!!!

Anyhow, any help on getting this monster put to bed (after this
form/report,
I am creating reports that I already know how to do) is HUGELY
APPRECIATED!!!

Many thanks in advance,

Jeff