A Look at Aggregate Queries and the SQL Aggregate Functions
What Is an Aggregate Query?
An aggregate query is a special type of query within
Access that allows you to group your data and perform specific mathematical
functions on those groupings. Since these are SQL functions they upsize
very well.
The SQL Aggregate Funtions:
The available SQL Aggregate Functions in an Aggregate
Query within Access are detailed below.
Group By – Group By does exactly what it says. It will group your query
data on each unique value in the set of fields listed as Group By.
Sum – This is another obvious function. This one will give
you a total of all the records in this field for the groupings indicated in
the Group By field.
Avg – Similar to the Sum function, this will give you the
average of all records in the specified fields within each grouping.
Min – Unlike the Sum and Avg functions this will return a
single value from the group of records. It will give the lowest value in
all the records in the specified field within each grouping.
Max – This function is similar to the Min function, it returns
a single value from all records within the grouping. It will return the
highest value within each grouping.
Count – The Count function simply counts the number of entries
within the designated field within each grouping.
StDev – This returns the standard deviation across all records in
each grouping.
Var – This is another measurement of deviation. This one is
the Variance. It gives the amount by which the value deviates from the
average.
First – Like the Min and Max functions, the First function does
no mathematical calculations, it simply returns a single value from the
group of records. The value returned is the first value for the field in
question within the grouping of records.
Last – The opposite of First, this simply returns the last
value entered in the field within each grouping.
Expression – This is the instruction to
tell Access to perform a different mathematical expression on each value
within the groupings. It allows the designer to make use of other functions
available within access.
Where – Those of you familiar with
the actual SQL language realize that this is your Where clause. This tells
Access that you don’t want to include the field in your recordset you merely
want to use it as a criteria to filter out some data out of the final
recordset. Bear in mind that fields in the Where clause do not show up in
the results of this type of query.
How Can I Use These?
Aggregate functions can be used alone or in bunches. The most important
thing to remember in an Aggregate query is that every field in your query
must be performing an aggregate function.
Another great advantage of using this is that you can perform multiple
functions on a single field. This way you can get all the data you need
about a particular grouping of data in a single query. Here is an example:
You
are the Sales Manager of your firm. You need to look at the Sales Data of
your Salespeople as part of their evaluation.
Let’s open the Northwinds database and click on the query tab. Open up a
new query and find the summation (this creates an Aggregate Query).
Locate the
Summation Icon, and click it. You will notice that your query now has an
extra line in the design grid entitled Total. Let’s pull in the Employees
and Orders Tables and the Order Details Extended Query. Bring in the First
name and last name fields and leave these set as group by, You can
concatenate the first and last names into an Employee Name field if you like
as well. Now bring in the Order Date, Extended Price, and Quantity Fields.
Set Order Date to Count in the Total row and select descending in the Sort
row. Next, Select Sum for both Extended Price and Quantity. Bring in the
same to fields and set them to Avg. Include Extended Price twice more, once
for Min and once for Max. Save and Execute the query.
Your results show you
all of the details about each Sales Person. You have the number of orders
they recorded, the total dollars in sales, their average order and average
dollar amount of each sale and their highest and lowest individual sale.
You should be seeing the beginnings of the power this function can have.
I hope you have
enjoyed this month’s article and would love to hear any feedback you may
have.
James
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
,
