Count function in a form

[Guest]

I am creating a commssion report which would involve volume incentives.

The compensations is as follows:

Widgets sold

1-15 $0
16-25 $25 each
26-36 $50 each
37+ 100 each

(e.g. if someone sells 20 widgets they get a bonus of 20 x $25 or $1000)

I want to do a count function on a field called "status". Based on the total
number of status (there are different kind) the bonus will be paid.

Any help would be appreciated.

 

[Michel Walsh]

Hi,


With a table, bonus, like:

UpTo, Cumul
15 0
25 25
36 25
999 50



Note that cumul is to be cumulate, such as for 37 items, the rebate is
0+25+25, or 50$, per item exceeding 36.


SELECT myTable.Item, SUM(Bonus.Cumul*(myTable.QtyBought-bonus.UpTo))
FROM mytable LEFT JOIN bonus
ON mytable.QtyBought >= bonus.UpTo
GROUP BY myTable.Item



Hoping it may help,
Vanderghast, Access MVP