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.
Figure A - Demonstration Table Of Product Sales And Budgets Figures
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
IIf([ProductName]="Cola",[Sales],Null) AS ColaSales
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.
Sum(IIf([ProductName]="Cola",[Sales],Null)) AS ColaSales,
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,
AS ColaSalesTotal, Sum(IIf([ProductName]="Cola",[Budgets],Null)) AS ColaBudgetTotal,
Avg(IIf([ProductName]="Cola",[Sales],Null)) AS ColaSalesMax
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.
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.