Conceptually, is this the proper approach?

[Dave]

I am allocating commissions among sales reps. A commission is allocated
among teams. Individual sales reps are organized into teams and share the
commission according to their percentage within the team.

For example, a check for $100 comes in and $40 is allocated to Team A and
$60 is allocated to Team B. Team A consists of Reps 1 and 2 whose splits
are 25% and 75% respectively. Team B consists of Reps 3,4,and 5 whose
splits are 33% each. Thus, the $100 is allocated among the reps as
follows: Rep1 - $10 ($40 x 25%), Rep 2 - $30 ($40 x 75%); Rep 3 - $20 ($60
x 33%); Rep 4 - $20 ($60 x 33%); and Rep 5 - $20 ($60 x 33%) .

A key point is that the check proceeds must be split twice: initially among
the teams and then among the team members (reps).

The initial split is performed mannually. The user enters the check total on
a form and then assigns each team their respective amounts. Based on the
amounts entered for the teams, the appropriate amounts are assigned to the
reps based on their share percentages which are stored in a table.

My main issue is: How do I code the commission allocation among the reps?

Keeping in mind that the allocation among the teams is a manual input by the
user and only the allocation among the reps must be coded, my initial
approach is to code the OnChange event of textbox that receives the team's
commission allocation. Thus, when an allocation to a team is input, code
fires that performs the allocation among the reps who are members of this
team.

However, I don't know of an easy way to perform the rep allocation in
Access.

My initial approach is to create an ADO connection and command object that
instantiate with OnChange, delete any existing records for this team and
check split, and then insert new records for this team and checksplit. (I
need to first delete any existing records in case the user is changing the
team allocation amount rather than inputting it for the first time.)

Can anyone tell me if conceptually this is the best approach? Or perhaps
there is a better/easier/more efficient way?

Thanks
Dave

------------------------
BTW, my essential schema looks like this:

CHECK table
CheckID
CheckAmount

CHECKSPLIT table
CheckSplitID
SplitID
CheckID
SplitAmount

SPLIT table
SplitID

REPSPLIT table
RepID
SplitID
SplitPercent

REP table
RepID

REPAMOUNT table
RepAmountID
RepID
CheckSplitID
RepAmount

The relationship between REP and CHECKSPLIT is many-to-many through the
junction table REPAMOUNT

The relationship between REP and SPLIT is many-to-many through the junction
table REPSPLIT

The relationship between CHECK and SPLIT is many-to-many through the
junction table CHECKSPLIT

The amount of commission allocated to each rep is calculated and inserted as
follows

INSERT repamount (repid, checksplitid, repamount)
SELECT r.repid, c.checksplitid, (c.splitamount * r.splitpercent)
FROM repsplit r JOIN checksplit c ON c.splitid=r.splitid

 

[Dave]

Whoops, I forgot my WHERE clause at the bottom of the original post.

And I need to code After_Update rather than the OnChange event.

The following seems to work:

Private Sub txtSplitAmount_AfterUpdate()

If Len(txtSplitAmount.Value) > 0 Then

Dim cmd As New ADODB.Command
Dim sql As String

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

'delete any existing records
sql = "DELETE * FROM RepAmount WHERE CheckSplitID = " &
txtSplitAmount.Value

cmd.CommandText = sql
cmd.Execute

'add new records
sql = "INSERT INTO repamount (repid, checksplitid, repamount) " _
& " SELECT r.repid, c.checksplitid, (c.splitamount *
r.splitpercent) " _
& " FROM repsplit r INNER JOIN checksplit c ON
c.splitid=r.splitid " _
& " WHERE c.checksplitid = " & txtSplitAmount.Value

cmd.CommandText = sql
cmd.Execute

Set cmd = Nothing

End If