AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890


Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at

Are you outside the United States?  We can ship multiple books worldwide in most countries.




Use calculated fields with the iif() function

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


How to use calculated fields with the iif() function

Determine employee bonus eligibility


In this example, we are looking for sales representatives who are eligible for a bonus.  To be eligible for a bonus, a sales rep needs to have accumulated sales of $5,000 or more for the year.  The SQL code in this example is long but easy.  First, notice that we use three fields only:  LastName, Bonus, and OrderDate.  We use the OrderDate field to filter orders for 2009 only.  Then, we use the lastname field with a GROUP BY clause to display results by employee name.  The last field is that of the Bonus.  Here, we use the iif() function with the syntax iif (expression, result if expression is true, result if expression is false) to actually make the calculations and determine bonus eligibility: 

IIf(Sum([unitprice]*[quantity])>5000,"Bonus","No Bonus") AS Bonus 

The iif() function above reads:  If the total amount of orders serviced by the sales rep exceeds $5,000, give the sales rep a bonus. Otherwise, no bonus.  The AS part means display this field name as "Bonus".  Do not pay attention to the joins in this example since we only use them to get fields from three different tables.  For a full overview of joins, see chapter 29. 


SELECT SalesReps.LastName, IIf(Sum([unitprice]*[quantity])>5000,"Bonus","No Bonus") AS Bonus


(SalesReps INNER JOIN Orders ON SalesReps.SalesRepID = Orders.SalesRepID) INNER JOIN ProductsOrders ON Orders.OrderID = ProductsOrders.OrderID


WHERE (((Orders.OrderDate) Between #1/1/2009# AND #12/31/2009#))


GROUP BY SalesReps.LastName