IIF Queries = Tricky Queries = Impress Your Boss

<< Click to Display Table of Contents >>

Navigation:  Queries  >

IIF Queries = Tricky Queries = Impress Your Boss

Garry Robinson

One of the solutions that I find myself using time and time again is providing a report with the totals in columns not in rows. In other words, provide a report just like your boss used to do it when the computer system was in Excel and not in Access.

To illustrate this issue, Figure A shows the data table (see demonstration database) and figure B shows the required answer with the totals for the different products shown by region in columns.
 

Note: This article is covered in more detail in this article called Tricky Queries

 

 

odc1i

Figure A - Demonstration Table Of Product Sales And Budgets Figures

 

 

odc2i

Figure B - Product Sales Totals by Regions

 

So how do I solve this problem ?

 

The method that doesn't seem to run into any dead ends is combining IIF functions and a consolidation query (Group By) as follows.  (Refer to Figures A, B and C) to see where the fields in the queries are being derived from.

First you need to setup the columns in a normal query as follows

 

SELECT Region,

IIf([ProductName]="Cola",[Sales],Null) AS ColaSales

FROM zWorld_Demo;

 

This will return all rows from the table but in the column known as ColaSales, it will only return the Sales result if the ProductName for that row is "Cola".  For other rows such as "Bottled Water" or "Iced Tea", the ColaSales column will return a null value.   This means that when we convert the ordinary query to a consolidation query, we can total all the values in the ColaSales column and we will get only the totals rows of data for "Cola" sales see the SQL below and Figure C for the design view.

 

SELECT Region,

Sum(IIf([ProductName]="Cola",[Sales],Null)) AS ColaSales,

FROM zWorld_Demo

GROUP BY Region;

 

odc3i

Figure C shows the basic IIF statement in design mode

 

Taking the IIF statement a little further

 

Now that you have the totals for Cola sales, copy the column in design view and paste it a number of times for the other ProductNames.   Then replace the   IIf([ProductName]="Cola" with IIf([ProductName]="Iced Tea" and you will be able to sum the Iced Tea totals etc.

This approach also gives you many other consolidation function options that you do not get if you use crosstab queries.   For one you can product totals from different columns in the raw data and you can use different consolidation functions in the same query.  The following SQL shows product totals by quarter for Cola Sales and Budgets and it also shows the maximum sale in each region during that period.

 

SELECT Region, Format([SalesDate],"yyyy-q") AS Quarter, 

Sum(IIf([ProductName]="Cola",[Sales],Null)) 

AS ColaSalesTotal, Sum(IIf([ProductName]="Cola",[Budgets],Null)) AS ColaBudgetTotal, 

Avg(IIf([ProductName]="Cola",[Sales],Null)) AS ColaSalesMax

FROM zWorld_Demo

GROUP BY Region, Format([SalesDate],"yyyy-q");

 

See the sample database for this example and an even more powerful example that uses your own functions to provide the group by column for the query.

Summing Up

 

The technique of combining IIF functions and consolidation queries is a very powerful feature because it can delivery column based queries and report reports that many managers are used to.  The conventional top down reporting that Access delivers is not always as readable as totals in columns.  So go and impress your boss with your newly structured queries.