Tricky Queries To Impress Your Boss

For Access 2007, 2003, 2002 and 2000

For this article, I poured over the many Access applications that I have written to find a useful Access topic that is not generally known by programmers.  The one that I came up with was the technique of using IIF functions in Access consolidation queries.  Hopefully these query tricks will let you come up with some new query/reporting formats that will impress your boss.

HOW TO SHOW TOTALS IN COLUMNS

One issues that you have may have to solve is to provide 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.

  << click to enlarge
Figure A - Demonstration Table Of Product Sales And Budgets Figures

Figure B - Product Sales Totals by Regions

So how would you go about solving this problem ?

Idea 1.  Setup a consolidation or group by query which would show the same results in rows rather than columns using a query such as the following

SELECT Region, ProductName, Sum(Sales) AS SumOfSales
FROM zWorld_Demo
GROUP BY Region, ProductName;

You could show this to the boss and they would never really understand why you have to show the results in rows and you would head back to the drawing board.

Idea 2.  Try a crosstab query such as the following SQL statement

TRANSFORM Sum(Sales) AS SumOfSales
SELECT Region
FROM zWorld_Demo
GROUP BY Region
PIVOT ProductName;

This is a very good approach in a lot of cases because it will guarantee that the query will show all the available products in the columns.  But as soon as you have to start showing the products in a required order, you need to get behind the properties of the crosstab query and into the mysterious world of crosstab column headers. 

Idea 3.   Do the separate totals in separate queries

If you are keen, you do your different totals using separate consolidation queries all with their own filter clause.  Once you have set them up, you can combine them using a common consolidated data field.  Be careful with this technique as it requires ou to properly handle missing data using outer joins and other tricks.

Idea 4.   Head over to managing the process using Visual Basic and recordsets

I guess I have already lost 9/10ths of the reading public on that one.  If all else fails, come back to this approach.

The IIF solution

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;

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.

Useful Further Reading and Resources

Open the Access help and contents file and search for
"aggregate functions"

Alternatively read an article on Consolidation Queries that I wrote in 1988.

  Your Sample Database Is Called   "trickyqueries.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Author Bio.

Garry Robinson has been involved in over a 100 Access projects, edits his companies web site at  http://www.vb123.com/  and is a contributing editor to the Smart Access magazine.  When Garry is not sitting at a keyboard, he can be found playing golf or swimming down at his local Sydney beach.  
Contact details  … www.gr-fx.com
 

Other Pages at VB123.com That You May Want To Visit

How To Make A Numerical Sequence in An Access Query
Taking Outlook and XML to Task in MS Access
Access Traps for the Naïve Developer

A Related Page on the Microsoft Blog

http://blogs.msdn.com/access/archive/2009/07/02/group-numbers-into-custom-ranges.aspx

 

Click on the button for the next help page in this Access Loop.