One of the 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.

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 you to properly handle missing data using outer joins and other tricks.

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

Loop through a list of regions

Export the region description and export a header for the products

Find the total for that region & export that single answer

end Region loop

If all else fails, come back to this approach.  It is only by knowing the limits of the query approaches, will you minimize using this approach.

Garry’s No 1 Approach – The IIF Query 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 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 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.