Code example 1:

In Products table, we compare UnitsInStock with ReorderLevel and conclude the result as:

• High - UnitsInStock is greater than ReorderLevel
• Low - UnitsInStock is less than ReorderLevel

Then, we want to know how many products are in High category and how many are in Low category.

SELECT IIf([UnitsInStock]<[ReorderLevel],'Low','High') AS [Stock Level], 
       Count([Stock Level]) AS [Product Count]
FROM Products
GROUP BY IIf([UnitsInStock]<[ReorderLevel],'Low','High');

Code example 2:

In this example, we want to know the average freight per order for both Domestic and International ship country. Assume that USA is domestic and all other countries are international.

• Domestic - USA
• International - All other countries

SELECT IIf([ShipCountry]='USA','Domestic','International') AS [Order Type], 
       Avg(Freight) AS [Avg Freight Per Order]
FROM Orders
GROUP BY IIf([ShipCountry]='USA','Domestic','International');

To sum up, in this tutorial we have illustrated how to use MS Access IIF function in SELECT statement to conditionally group records in Access database table and calculate aggregated values. Part 3 of IIF function will demonstrate how to use IIF function in ORDER BY clause.


Copyright© GeeksEngine.com