Inside This ArticleUse MS Access IIF function to conditionally calculate field values
In Part 4, we looked at how to use IIF function with GROUP BY clause. The format of query result in Part 4 is not
very eye friendly. The query only groups the UnitsInStock totals vertically by Continent.
This results in more records, making comparisons of UnitInStock totals between different categories and
continent more difficult.
What do we want
In Suppliers table, we want to calculate the number of units in stock for each product category
and then list the result by continents horizontally. This is what Access crosstab query type is for.
Countries and continents
- Asia-Pacific: Singapore, Japan, Australia
- America: USA, Canada, Brazil
- Europe: UK, Spain, Sweden, Germany, Norway, Denmark, Netherlands, Finland, Italy, France
The data result format showing here is PivotTable format. Access crosstab query type is used for the same purpose - making query result set easier to read when comparing data across columns.
| Query Name: |
FuncTest_Grouping_Complex3 |
| Code Logic: |
To get our desired result format, we wrap IIF function inside SUM function.
The first SUM will add up the UnitsInStock for each country that belongs to Europe by
using IIF function inside the SUM function. The second SUM applies the same logic for
America. The last SUM does it against Asia-Pacific countries.
In GROUP BY, we specify product category so that only the same category is grouped.
|
| SQL View: |
SELECT Categories.CategoryName,
Sum(IIf([suppliers].[Country] IN (
'UK',
'Spain',
'Sweden',
'Germany',
'Norway',
'Denmark',
'Netherlands',
'Finland',
'Italy',
'France'), [products].[unitsinstock])) AS Europe,
Sum(IIf([suppliers].[country] IN (
'USA',
'Canada',
'Brazil'), [products].[unitsinstock])) AS America,
Sum(IIf([suppliers].[country] IN (
'Japan',
'Australia',
'Singapore'),[products].[unitsinstock])) AS [Asia-Pacific]
FROM Suppliers INNER JOIN
(Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID
GROUP BY Categories.CategoryName;
|
|
Datasheet View:
|
8 records returned:
|
In Part 6 of IIF tutorial we'll fine-tune the query by using NZ function to eliminate NULL values
which show blanks in the result set above.
Copyright© GeeksEngine.com
|
Inside This Article
Related Articles:
Other Recent Articles from the MS Access category:
|
