Help on Conditional SUM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the sales information. The form will run a query and list
the Sales and commisions earned by the sales persons. Commisions are paid
after 2 months of sales. A query is run by specifying a sales person name and
result is posted to form as shown.


Sales : John Doe

Company Sales dated Commission Pay_date
ABC 1,000 12/1/2006 10.00 2/1/2007
ABC 2,000 1/5/2007 20.00 3/1/2007
CBA 3,500 2/2/2007 35.00 5/1/2007


Sales up-to-date : 6,500.00 <<<<< =SUM(Sales)
Commission paid: 30.00 <<<<< how to program this part
Commission pending: 35.00 <<<<< how to make this happen

Question 1. How do I calculate the commissions paid to inlcude only the paid
commsions?

Question 2. How do I calculate the commissions pending by including only
commissions to be paid in future?

Thanks for the help.
pw
 
Perry said:
I have a table with the sales information. The form will run a query and list
the Sales and commisions earned by the sales persons. Commisions are paid
after 2 months of sales. A query is run by specifying a sales person name and
result is posted to form as shown.


Sales : John Doe

Company Sales dated Commission Pay_date
ABC 1,000 12/1/2006 10.00 2/1/2007
ABC 2,000 1/5/2007 20.00 3/1/2007
CBA 3,500 2/2/2007 35.00 5/1/2007


Sales up-to-date : 6,500.00 <<<<< =SUM(Sales)
Commission paid: 30.00 <<<<< how to program this part
Commission pending: 35.00 <<<<< how to make this happen

Question 1. How do I calculate the commissions paid to inlcude only the paid
commsions?

Question 2. How do I calculate the commissions pending by including only
commissions to be paid in future?
Click to expand...


Try something like:
=Sum(IIf(Pay_Date <= Date(), Commission, 0)
and
=Sum(IIf(Pay_Date > Date(), Commission, 0)
 
Back
Top