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

 
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'));

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.

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