Running sums and other help

[grantschneider]

I have created a running sum that calculates how close each [VP] is to
their target number of sales (Budget). As they approach their target,
they move into tiers(there are four of them). Tier 1 is at 50% of
their budget, Tier 2 is at 75%, tier 3 @ 90% and tier 4 @ 110%. The
higher the tier, the more commission per sale they are paid @ 100%,
125%, 150% and 175% respectively.

Incase I haven't been clear enough, if someone's budget is 100, for
their first 50 sales they make 100% commision, then the next 25 sales
they get 125% and so on.


Is there an easier way of doing this than writing a whole lot of IF
statements to come up with the tier and then even more IF statements
(and columns) to come up with their commission per sale?

Thanks.

My Code:

SELECT [Card Sale List].ID, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].[Payment Date], (Select Sum([# of
Cards])
FROM [Card Sale List] as Tmp
WHERE Tmp.VP=[Card Sale List].VP
And Tmp.[Payment Date] <=[Card Sale List].[Payment Date]) AS
RunningSum, [Card Budgets].Budget, [RunningSum]/[Budget] AS [Position]
FROM [Card Sale List] INNER JOIN [Card Budgets] ON [Card Sale List].VP
= [Card Budgets].VP;



Once I figure out which tier they are in, I will use a form that will
allow me to change both the Tier percentages (when they change to a
new tier) as well as the commission 'multipliers' (what the standard
commission is multiplied by) and then multiply them by the net
commision at [Total Card].[VP Com].