Consolidation Queries In Microsoft Access |
When your data builds up to a substantial number of records, at some stage you are going to want to analyze that data. One of the more useful ways to start understanding your information quickly is through the Consolidation (Totals) Queries. These types of queries are important to understand as a lot of programming issues can actually be resolved with a better understanding of their full potential. This article demonstrates a number of different techniques that you deploy using consolidation queries including " IIF " & "Running Sums" & "Embedded Functions".
Microsoft Access is very powerful at
consolidating data as the Query/SQL environment supports functions and
complex expressions. This
gives the Access front end database considerable power to explore data in
many compatible data sources. Most
of the queries that are demonstrated here can be generated in the
shareware data mining tool Graf-FX that was developed by the Author.
In addition these types of consolidation queries can be used as the
row source for Microsoft Graph object embedded in Access forms.
To try other styles of consolidation queries, experiment with the
Graph Wizard when generating new forms in Access.
The Demonstration Database
The demonstration database is called
totals.mdb. Inside that
database is a table called TblSalesResults plus all the queries and
functions that are demonstrated. Listing
1 shows the demonstration data used
in this article. The data
consists of the sales and
budgeted results for three soft drink products in different regions of
A number of lines of
information from the tblSalesResults table
If we were to setup a consolidation query on this table to show the sales results by product and region, we would have the following SQL query and sample result as shown in Listing 2
SELECT productName, region, Group By Columns
Sum(sales) AS totSales Aggregated values
FROM TblSalesResults Table/Query source
GROUP BY productName, region; Group By Columns
ORDER BY Sum(sales);
Order By Clause
A sales totals query
with descriptions of the different components of the consolidation query
plus some lines of output
a general guide, consolidation queries can be broken up into 5 different
By Columns These
are the columns that you want to Consolidate On. Choosing more than one column will create aggregate totals
for all the combinations of the different Group By columns found in the
source data. Notice that the
columns are repeated twice in the SQL statement.
If you want to see the grid view of the same query, open the
demonstration database and look at query qTot_Listing2
in design mode.
Values If a column is not
being grouped on, it should be subject to an aggregate function.
These aggregate functions fall into two groups. Those
that require a number field Sum, Avg, StDev, Var, and those that also work
on text data Count, Min, Max, First, Last.
If you wish to fund out what these do, enter Aggregate in Access
source Simply the
table or query where the data is derived from.
that restrict the rows of data that are being analyzed (filters)
Order By You can sort the results of the query by any output field in the query and more if you choose. If you do not select a column, the query will sorted by the first Group By field.
Exceptions to these rules include the
use of expressions to combine fields, the having clause rather than the
where clause and multiple table/query sources where the sources are joined
on common fields. The remainder
of the document demonstrates
ways that you can achieve more with your consolidation queries
information in tables is one area where it would be unusual to analyze
results by consolidating on a groups of data in the raw date form.
Usually you would want to look at weekly, monthly or quarterly
results whilst the actual information would be stored with one or more
entries per day.
Using the access format
function, the SQL shown in Listing 3 produces the following
table of output
Listing 3 An access function used in a query to show data consolidated by month.
So by consolidating the dates into
months, we now can start analysing the data to look for possible patterns. Note two important things with this query.
Firstly the Years are shown first followed by the months as
numbers. This guarantees that
the output will be sorted sequentially.
Also when you start building these functions, always use the full 4
digit year or you will be introducing a Year 2000 bug into your code as
year 2000 will show as "00" and sort first.
It is possible to undertake
consolidations on number fields (even decimals) by the use of the
following techniques. To
group the sales totals into low, medium and high results you can use an IIF
function in your query as follows
4 shows this statement
utilized in a SQL expression that counts the number of sales that occur in
SELECT IIf([sales]<=1000,"Low",IIf([sales]<=3000,"Medium","High")) AS Classification, Count(salesDate) AS numSales
into separate categories and viewing the number of occurrences
You can achieve the same result as the
IIf expression by transferring the expression into a callable function.
Also IIf functions in queries can reduce performance of a query on
big database searches. Listing 5 shows both a function that would replace the IIF
expression plus its inclusion in a SQL statement.
Function FX_Classify (VarSalesVal As Variant) As String
This function classifies Sales numbers into string descriptions
Select Case VarSalesVal
SELECT FX_Classify([sales]) AS Classification,
Count(salesDate) AS numSales
GROUP BY FX_Classify([sales]);
Listing 5 Classifying numbers into separate categories using a function embedded in a query
Normal aggregate functions on numbers
are Average, Sum, Standard
Deviation and Variance. It should be noted that these only apply to data that has an
actual value. If the result
is null, then it is not included in the aggregate total.
If you have a field that is designated as a string field but only
has numerical results in it, you
can compute the numerical aggregate by enclosing the field in an Access
function called cDBL.
SELECT productName, Avg(CDbl([sales])) AS avgSales
GROUP BY productName
You can do more with the aggregate
functions by adding expressions to the query.
Listing 7 shows how to
calculate an average for a field that is weighted on the value of data in
another field. For this
expression, we have the two fields in each row being multiplied together
to form a total with all those totals being summed together.
At the same time this expression also calculates the total of all
sales. The weighted average
is returned as the division of the two sums.
Sum([sales]*[budgets])/Sum([budgets]) AS WgtAvgSales
GROUP BY productName;
8 shows the how you can combine aggregate results in a query. The expression in this example uses the results from two of
the other fields. It is
important to come up with a query column naming convention when you start
using expressions in queries as debugging the formula in the queries can
SELECT DISTINCTROW productName,
Sum(sales) as SalesTot, Sum(budgets) as BudgetTot, [SalesTot]-[BudgetTot] as SalesPerf
GROUP BY productName;
There is no inbuilt function in Access
to calculate running totals. The
following technique will produce output that returns totals by day for the
different regions in the tblSalesResults table.
These need to be setup on an individual basis but if you need
cumulative output, it offers an option.
The result in this case is a Totals query by virtue of the fact
that the inbuilt function provides the aggregate total through the DSum
function. These style of
queries are slow but provide far less code than a comparable RecordSet
solution written in Access basic.
SELECT DISTINCTROW region, productName, salesDate, RSum_FX([salesDate],[productName],[region]) AS RSumSales, sales
ORDER BY region, productName, salesDate;
Function RSum_FX (dateVal As Variant, _
As Variant, regionVal As Variant) As Variant
Dim whereStr As String
If IsNull(dateVal) Then
DSum("[Sales]", "[tblSalesResults]", whereStr)
Listing 9 A query to calculate running totals for sales which uses a special function called RSUM_FX. The query output, SQL and the access function are shown
The most important part of this function is the building of the where string for the DSum routine. For the date handling it was easier to translate the date back to its stored integer number rather than transferring the dates to a common format understandable by Access Basic.
Knowledge of the different ways that
you can utilise Consolidation Queries is quite important to those of you
who aim to undertake data analysis.
Initially you must come to terms with the way that data is
aggregated. Then experiment
with the use of functions to aggregate your data in ways not provided by
the data that is stored in the database. This will probably take you into utilizing Access Inbuilt
functions such as format, IIF statements and even then to your own custom
functions. Finally when you
have those concepts grasped, you can adopt expressions in your queries to
do your aggregate calculations.
An extension on these techniques is the
joining of queries using the values derived in group by fields.
I recently used this technique in a mining application where the
data was required to be reported by month but actually was stored in
separate daily, weekly and monthly results tables. Rather
than designing a system that would have been based on visual basic code
and record set manipulation, the software was setup using consolidation
queries and joins between them. Not understanding consolidation queries
would have resulted in a more complicated solution for the client.
Contact details … +61 2 9340 7789 Web http://www.gr-fx.com/
Note: The Access 97 version of this download is in the Access 97 Frozen pack
Other Related Articles At vb123.com
To Create A Crosstab Query