Running Totals

[Matt Reed]

Hello, I am trying to run a query on sales figures where the salesmen's
commission varies with the total sales for the year.

The salesmen's commission starts at a rate of 10%, once their total sales
for the year goes over $10,000 the rate goes to 15% and when their total
sales goes over $50,000 their rate goes to 20%.

Fields
[SaleID] [SaleDate] [SaleAmount][SalesmenComm]

So I thought if I could calculate a running total of sales then I could
calculate the commission off of that field.

Can this be done in a query?
If so how?

Thanks for any help

Matt

 

[[MVP] S.Clark]

Is the increase in commission retroactive for all sales for the year, or
only increased for the tiers?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Matt Reed]

Thanks for the reply, was out of touch for a few days.

Commission increase is only from the tiers, or in other words, when they
reach a certain dollar amount of sales their commision rate changes from
then forward.

Thanks for your help.

Matt


Is the increase in commission retroactive for all sales for the year, or
only increased for the tiers?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[[MVP] S.Clark]

I think I would create a commission history table that stores the date and
commission amount for each salesperson. (I would go these length for the
audit trail.)

So, every salesperson will have one record with the info.

i.e.

SalespersonID Date Amount
123 1/1/2004 10%

Next, on a sale-to-sale, nightly, weekly, monthly, and/or quarterly basis,
run a script to find the current YTD Sales total per salesperson, then
update their Commission History. Those that never move stay at the
beginning rate.

Thanks for the reply, was out of touch for a few days.

Commission increase is only from the tiers, or in other words, when they
reach a certain dollar amount of sales their commision rate changes from
then forward.

Thanks for your help.

Matt


Is the increase in commission retroactive for all sales for the year, or
only increased for the tiers?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hello, I am trying to run a query on sales figures where the salesmen's
commission varies with the total sales for the year.

The salesmen's commission starts at a rate of 10%, once their total sales
for the year goes over $10,000 the rate goes to 15% and when their total
sales goes over $50,000 their rate goes to 20%.

Fields
[SaleID] [SaleDate] [SaleAmount][SalesmenComm]

So I thought if I could calculate a running total of sales then I could
calculate the commission off of that field.

Can this be done in a query?
If so how?

Thanks for any help

Matt