Calculated value of sale determines commission

[CeeBeeSky]

I am trying to set up a commissions report.

Here is my commission qualifier:

If the $ value of the items sold (this is a calculated sum) create a total
sales amount of more than $2000, commission is due. If it is less, no
commission is due. What would be the best way to handle this argument?

 

[Duane Hookom]

I would create a table (tblCommissionPoint) with a single numeric field
(CommissionPoint) and a record containing 2000. Add this table to your
report's record source query so you can use a control source in a footer like:
=IIf(Sum(Sales)>[CommissionPoint],....,0)

 

[CeeBeeSky]

This worked great, thank you. But I had to put it into the reports "Invoice
Footer" field. I created the argument in the control source. I couldn't
find a way to make it work in the query since many items make up one invoice
total. When I tried to implement in a query, it once again looked at
individual item sales, not the total of the invoice.

Since I implemented it into the invoice my problem is that the grouping
which this sort is under (Salesman) does not calculate the total properly.
That is, the individual sale is totally across properly but to sum the
grouping, it does not. Is there any way around this?

Thanks for your help thus far.

CeeBeeSky

I would create a table (tblCommissionPoint) with a single numeric field
(CommissionPoint) and a record containing 2000. Add this table to your
report's record source query so you can use a control source in a footer like:
=IIf(Sum(Sales)>[CommissionPoint],....,0)
--
Duane Hookom
Microsoft Access MVP

I am trying to set up a commissions report.

Here is my commission qualifier:

If the $ value of the items sold (this is a calculated sum) create a total
sales amount of more than $2000, commission is due. If it is less, no
commission is due. What would be the best way to handle this argument?