Inside This ArticleUse MS Access IIF function in GROUP BY clause
In this tutorial, IIF function is explored further to show how to use it
in a GROUP BY clause for conditionally grouping operations as well as using with aggregate
functions such as count, sum, average...
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.
Query Name |
Qry_Func_IIF_3 |
Logic |
IF...THEN...ELSE... |
IIF Code |
IIf (UnitsInStock < ReorderLevel, 'Low', 'High') |
SQL view |
SELECT IIf([UnitsInStock]<[ReorderLevel],'Low','High') AS [Stock Level],
Count([Stock Level]) AS [Product Count]
FROM Products
GROUP BY IIf([UnitsInStock]<[ReorderLevel],'Low','High');
|
Datasheet View |
2 records returned: |
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
Query Name |
Qry_Func_IIF_4 |
IIF Code |
IIf (ShipCountry = 'USA', 'Domestic', 'International') |
SQL View |
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');
|
Datasheet View |
2 records returned: |
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
| Inside This Article Related Articles:
Other Recent Articles from the MS Access category:
|