vb123.com.au  

  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". 

by Garry Robinson

Technology Introduction

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 Australia.

productName	SalesDate	State	Region	sales	Budgets
Orange	14/01/95	NSW	Goulburn	220	240
Lemonade	14/01/95	NSW	Sydney	940	1000
Orange	14/01/95	NSW	Sydney	1000	960
Cola	14/01/95	NSW	Sydney	4000	3600

Listing 1  A number of lines of information from the tblSalesResults table

Consolidating

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

WHERE ((state="NSW")      Where Clause

GROUP BY productName, region;        Group By Columns

ORDER BY Sum(sales);      Order By Clause

 

ProductName	region	totSales
Cola	Goulburn	12160
Cola	Sydney	82200
Lemonade	Goulburn	3700
Lemonade	Sydney	17858

Listing 2  A sales totals query with descriptions of the different components of the consolidation query plus some lines of output

 As a general guide, consolidation queries can be broken up into 5 different components. 

Group 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.

Aggregated 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 help.

Table/Query source   Simply the table or query where the data is derived from.

Where Clause   Criteria 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

Advanced Group By

Date   related 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

SELECT DISTINCTROW Format([SalesDate],"yyyy-mm") AS SalesMonth,

Sum(sales) AS TotSales

FROM tblSalesResults

GROUP BY Format([SalesDate],"yyyy-mm");

SalesMonth	TotSales
1995-01	31560
1995-02	33340
1995-03	31584
1995-04	33358

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.

Classifying Numbers

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

IIf([sales]<=1000,"Low",IIf([sales]<=3000,"Medium","High"))

Listing 4  shows this statement utilized in a SQL expression that counts the number of sales that occur in each classification

SELECT   IIf([sales]<=1000,"Low",IIf([sales]<=3000,"Medium","High")) AS Classification, Count(salesDate) AS numSales

FROM   tblSalesResults

GROUP BY   IIf([sales]<=1000,"Low",IIf([sales]<=3000,"Medium","High"));

Classification	numSales
High	32
Low	181
Medium	86

Listing 4  Classifying numbers into separate categories and viewing the number of occurrences

Using Functions

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
        Case Is <= 1000
            FX_Classify = "Low"
        Case 1000 To 3000
            FX_Classify = "Medium"
        Case Is > 3000
            FX_Classify = "High"
        Case Else
            FX_Classify = "Unknown"

    End Select

Exit Function

SELECT   FX_Classify([sales]) AS Classification,

Count(salesDate) AS numSales

FROM    tblSalesResults

GROUP BY FX_Classify([sales]);

Listing 5 Classifying numbers into separate categories using a function embedded in a query

Aggregated Values

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

FROM TblSalesResults

GROUP BY productName

Listing 6 Computing an average on a string character field

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.

SELECT   productName,

Sum([sales]*[budgets])/Sum([budgets]) AS WgtAvgSales

FROM tblSalesResults

GROUP BY productName;

Listing 7   Weighted Average expression

Listing 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 be difficult.

SELECT DISTINCTROW productName,

Sum(sales) as SalesTot, Sum(budgets) as BudgetTot, [SalesTot]-[BudgetTot]  as  SalesPerf

FROM tblSalesResults

GROUP BY productName;

Listing 8   Combing the results from other aggregate functions into a new output field

Cumulative Expressions

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.

region	ProductName	salesDate	RSumSales	sales
Sydney	Cola	14/01/95	4000	4000
Sydney	Cola	28/01/95	8000	4000
Sydney	Cola	14/02/95	12260	4260
Sydney	Cola	28/02/95	16520	4260
Sydney	Cola	14/03/95	20320	3800
Sydney	Cola	28/03/95	24120	3800

SELECT DISTINCTROW region, productName, salesDate, RSum_FX([salesDate],[productName],[region]) AS RSumSales, sales

FROM tblSalesResults

ORDER BY region, productName, salesDate;

Function RSum_FX (dateVal As Variant, _

 ProductVal As Variant, regionVal As Variant) As Variant 

   Dim whereStr As String  

   If IsNull(dateVal) Then
     RSum_FX = Null
   Else
  whereStr = "clng(salesDate) <= " & CLng(dateVal) & _
    " and productName = '" & ProductVal & _
    "'  and region = '" & regionVal & "' "

     RSum_FX = DSum("[Sales]", "[tblSalesResults]", whereStr)
   End If

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.

Conclusion

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.

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. The understanding of these methods of manipulating consolidation queries came during the development of Graf-FX, a shareware data mining tool designed for Access 2, 95 and 97. Garry also has written over 40 geological, metallurgical and environmental solutions in Access.  

Contact details  …  +61 2 9665 2871    Web http://www.gr-fx.com/ 

  Your Sample Database Is Called   "consolidate.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.

 

Other Related Articles At vb123.com

How To Create A Crosstab Query
How To Make A Numerical Sequence in An Access Query
Tricky Queries To Impress Your Boss
Exploring Your Data With Subdatasheets
Backing Up Your Access Data With XML
Form Based Selection Criteria For Queries using Combo Boxes
 

External Reference

FMS article on reporting monthly data without code

Click this button for the next article in the loop   

Published  1998-10