Inside This ArticleA complex example of MS Access IIF function used within GROUP BY clause
This tutorial demonstrates a complex example of using IIF function with GROUP BY clause.
What do we want
In Suppliers table, we want to group suppliers into three continents: Asia-Pacific, America, and Europe.
Then, for each product category, we calculate the number of units in stock for each continent.
- Asia-Pacific: Singapore, Japan, Australia
- America: USA, Canada, Brazil
- Europe: UK, Spain, Sweden, Germany, Norway, Denmark, Netherlands, Finland, Italy, France
Query Name: |
FuncTest_Grouping_Complex1 |
Code Logic: |
The first IIF checks if the country is a European country. The second IIF checks
if the country is an American country. Everything else will automatically become
Asia-Pacific countries (No IIF is needed for it).
In order to group the result by continents, we need to select GROUP BY in the
Design View. If you prefer to deal with SQL directly, switch to SQL View and copy the entire
IIF statement and paste into the GROUP BY clause but leave out the field alias - AS [Supplier Continent].
To calculate total units in stock, use SUM function.
|
SQL View: |
SELECT Categories.CategoryName AS [Product Category],
IIf([suppliers].[Country]='UK'
Or [suppliers].[Country]='Spain'
Or [suppliers].[Country]='Sweden'
Or [suppliers].[Country]='Germany'
Or [suppliers].[Country]='Norway'
Or [suppliers].[Country]='Denmark'
Or [suppliers].[Country]='Netherlands'
Or [suppliers].[Country]='Finland'
Or [suppliers].[Country]='Italy'
Or [suppliers].[Country]='France', 'Europe',
IIf([suppliers].[country]='USA'
Or [suppliers].[Country]='Canada'
Or [suppliers].[Country]='Brazil', 'America', 'Asia-Pacific'))
AS [Supplier Continent],
Sum(Products.UnitsInStock) AS UnitsInStock
FROM Suppliers INNER JOIN
(Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID
GROUP BY Categories.CategoryName,
IIf([suppliers].[Country]='UK'
Or [suppliers].[Country]='Spain'
Or [suppliers].[Country]='Sweden'
Or [suppliers].[Country]='Germany'
Or [suppliers].[Country]='Norway'
Or [suppliers].[Country]='Denmark'
Or [suppliers].[Country]='Netherlands'
Or [suppliers].[Country]='Finland'
Or [suppliers].[Country]='Italy'
Or [suppliers].[Country]='France', 'Europe',
IIf([suppliers].[country]='USA'
Or [suppliers].[Country]='Canada'
Or [suppliers].[Country]='Brazil', 'America', 'Asia-Pacific'));
|
Datasheet View:
|
21 records returned:
|
Simplify the SQL
When you look at the SQL presented above, you may realize that you can simplify it
to achieve the same result. This can be done by using IN operator.
Query name: Func_Grouping_Complex2
SELECT Categories.CategoryName AS [Product Category],
IIf([suppliers].[Country] IN (
'UK',
'Spain',
'Sweden',
'Germany',
'Norway',
'Denmark',
'Netherlands',
'Finland',
'Italy',
'France'), 'Europe',
IIf([suppliers].[country] IN (
'USA',
'Canada',
'Brazil'), 'America', 'Asia-Pacific'))
AS [Supplier Continent],
Sum(Products.UnitsInStock) AS UnitsInStock
FROM Suppliers INNER JOIN
(Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID)
ON Suppliers.SupplierID = Products.SupplierID
GROUP BY Categories.CategoryName,
IIf([suppliers].[Country] IN (
'UK',
'Spain',
'Sweden',
'Germany',
'Norway',
'Denmark',
'Netherlands',
'Finland',
'Italy',
'France'), 'Europe',
IIf([suppliers].[country] IN (
'USA',
'Canada',
'Brazil'), 'America', 'Asia-Pacific'));
Food for thoughts
To build a true relational database, we may want to build a Continents table that contains data for all the continents.
In Suppliers table, add a new column ContinentID which is the foreign key pointing back to the Continents table.
Building the Continents table serves many purposes. One of them is to define
a data source that can be referred at any time without hard-coding the continent
names in your source code such as SQL or VBA.
In our example above, we can join Continents with Suppliers table and write a
SELECT statement to replace the code inside the IN operator. This is called sub-query.
It is also a good idea to separate out all countries into its own table.
In systems analysis, this is a normalization process. Sometimes you may need to
de-normalize tables to speed up query response time, but this is outside the scope of this tutorial.
What's next
The query result showing in Datasheet View has listed Supplier's Continent in the second, third, and fourth column.
It's nice for presentation purpose that we can list each continent in its own column.
Category Name |
Europe |
America |
Asia-Pacific |
Beverages |
324 |
203 |
32 |
Condiments |
45 |
372 |
90 |
Confections |
340 |
17 |
29 |
Dairy Products |
393 |
0 |
0 |
Grains/Cereals |
244 |
0 |
64 |
Meat/Poultry |
0 |
136 |
29 |
Produce |
26 |
15 |
59 |
Seafood |
396 |
208 |
97 |
This can be easily achieved if we copy the query result into Excel spreadsheet and create PivotTable for
data analysis. But to work with Access, we need to tweak our query to achieve the desired result.
This technique is demonstrated in next tutorial Part 5 of IIF function.
Copyright© GeeksEngine.com
| Inside This Article Related Articles:
Other Recent Articles from the MS Access category:
|