AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

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

Discussion:

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. 

Code:

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

FROM

(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
 

Result: