"The Access 2007 version of this article and download is in The Toolbox"
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.
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
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
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 and 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
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.
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
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.
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
Access help and contents file and search for
Alternatively read an article on Consolidation Queries that I wrote in 1988.
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 …
Other Pages at VB123.com That You May Want To Visit