Microsoft Access 2010 - Lesson 30: Enhancing Queries

Using Functions

Introduction

To enhance the result produced by a query, you can use some of the built-in functions of Microsoft Access, including those we saw in Lessons 11-14. You can use a function to control the values that would display in the query or you can include the functions in the condition set to filter the values.

To control how the values would display in the query, start a query in Design View or open a query in Design View. In the bottom section of the window, in the box of the field name, type the expression. For example, if you have a column named Gender and that display the genders as Male or as Female but you want to display only M or F respectively, you can use the Left() function in an expression as Left(Gender, 1):

To use a function in a criterion, open the query in Design View and select the column(s) you want. In the lower section of the window, click the Criteria box that corresponds to the column that will hold the criterion and type the expression that includes the function. For example, on a list of students that includes their dates of birth in a column named DOB, to get the list of students born in 1994, you would set the condition as Year([DOB])=1994. Here is an example:

Besides the functions we reviewed already, there are many other functions available in Microsoft Access. For example, a domain-based function is used to get a value from another object and deliver it to the object in which it is being used or called. The general syntax of these functions is:

To perform its operation, a domain-based function needs three pieces of information, two of which are required (the first two arguments) and one is optional (the third argument).

when calling one of these functions, you must specify the value of the column you want to retrieve. This is provided as the WhatValue in our syntax. This argument is passed as a string.

The FromWhatObject is the name of the object that holds the value. It is usually the name of a form. This argument also is passed as a string.

The third argument, WhatCriteria in our syntax, specifies the criterion that will be used to filter the WhatValue value. It follows the normal rules of setting a criterion.

Domain First: If you want to find out what was the first value entered in the cells of a certain column of an external form or report, you can call the DFirst() function.

Domain Last: The DLast() function does the opposite of the DFirst() function: It retrieves the last value entered in a column of a form or report.

Domain Sum: To get the addition of values that are stored in a column of another form or report, you can use the DSum() function.

Domain Count: The DCount() function is used to count the number of values entered in the cells of a column of a table.

Domain Average: The DAvg() function calculates the sum of values of a series and divides it by the count of cells on the same external form or report to get an average.

Domain Minimum: The DMin() function is used to retrieve the minimum value of the cells in a column of an external form or report

Domain Maximum: As opposed to the DMin() function, the DMax() function gets the highest value of a series of cells in the column of an external form or report.

There are various types of statistics you may want to get from this list: you may want to know the number of students registered in the school, you may want to know the number of girls or the number boys, you may want to know how many students were born in each year, you may want to know the average age of the students, you may want to know the oldest or the youngest students, or you may want to know the number of students from each ZIP code. To assist you with getting these statistics, Microsoft Access provides a type of query called a summary query.

A query is referred to as summary if it provides one or various analytic statistics about the records.

As always, when creating a query, you can use the Query Wizard or display one in Design View. If you are working in Design View, to make a query summarize its data:

After applying the Totals feature, each column of the query would become equipped with a row named Total.

Although you can create a summary query with all the fields or any field(s) of a query, the purpose of the query is to summarize data, not to review the records, which you would do with a normal select query. For a good summary query, you should select a column where the records hold categories of data. This means that the records in the resulting list have to be grouped by categories.

To support summary queries, the SQL provides the GROUP BY clause. It means where the records display, they would be grouped by their categories. For example, if you want to get the number of students by gender from a list of students, you would select the column that holds that information, but you can select other columns also:

As stated already, the purpose of a summary query is to provide some statistics. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant:

If you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once:

Summarizing the Values

To get the types of statistics you want, in the Design View of the query, add the same column one more time, and click the Total box that corresponds to the column:

To get the types of statistics you want, in the Design View of the query, add the same column one more time, and click the Total box that corresponds to the column

In reality, a summary query uses some of the functions that ship with Microsoft Access:

Count: Microsoft Access uses the Count() function to count the number of occurrences of the category in the column and produces the total
SELECT Students.Gender, Count(Students.Gender) AS CountOfGender FROM Students GROUP BY Students.Gender;
First: Microsoft Access uses the First() function to get the first occurrence of the value in the category
Last: Microsoft Access uses the Last() function to get the last occurrence of the value in the category
Consider the following table that shows the list of employees and the time they worked:

Consider the following query that wants to summarize the time worked:

If the column holds numeric values:
- Sum: The Sum() function is used to sum up the values in the category
```
SELECT TimeKeepers.EmployeeNumber,
Sum(TimeKeepers.TimeWorked) AS SumOfTimeWorked
FROM TimeKeepers
GROUP BY TimeKeepers.EmployeeNumber;
```
- Avg: The sum of values in a category would be divided by the number of occurrences in that category to get the average. Here is an example:
```
SELECT TimeKeepers.EmployeeNumber,
Avg(TimeKeepers.TimeWorked) AS AvgOfTimeWorked
FROM TimeKeepers
GROUP BY TimeKeepers.EmployeeNumber;
```
- Min: The lowest value of the category would be produced from the Min() function
  
  The result is the lowest time worked for each employee:
- Max: The highest value of the category would be produced using the Max() function. Here is an example:
```
SELECT TimeKeepers.EmployeeNumber,
Min(TimeKeepers.TimeWorked) AS MinOfTimeWorked,
Max(TimeKeepers.TimeWorked) AS MaxOfTimeWorked
FROM TimeKeepers
GROUP BY TimeKeepers.EmployeeNumber;
```
- StdDev: The StDev() function is used to calculate the standard deviation of all numeric values of a group. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group. Here is an example:
```
SELECT TimeKeepers.EmployeeNumber,
StDev(TimeKeepers.TimeWorked) AS StDevOfTimeWorked
FROM TimeKeepers
GROUP BY TimeKeepers.EmployeeNumber;
```
  The standard deviation of the values of the category would be calculated using the StdDev() function
- Var: The Var() function calculates the statistical variance of all numeric values of a group. If there is no value or the same value in the considered group, this function returns NULL. Here is an example:
```
SELECT TimeKeepers.EmployeeNumber,
Var(TimeKeepers.TimeWorked) AS VarOfTimeWorked
FROM TimeKeepers
GROUP BY TimeKeepers.EmployeeNumber;
```
  The statistical variance of the values in the category would be calculated

Expression: Consider the following query that we saw earlier:

Imagine that you want to get the percentage of occurrences of each gender. To get such a value, you can write an expression such as (Count(Gender) / 164) * 100. If you want to do this in a summary query, you can specify the Total type as Expression. Here is an example:
```
SELECT Students.Gender,
Count(Students.Gender) AS CountOfGender,
(Count([Gender])/164)*100 AS Percentage
FROM Students
GROUP BY Students.Gender;
```
Where: Notice that the above query has an empty (or null) record. This makes that record quite useless for the query. We know that you can set a condition in the Criteria box of the column used as the reference, which in this case would be Gender. Instead of using the referential column, you can add the same column one more time, set its Total to Where and, in its Criteria box, enter a WHERE type of statement. Also, this additional column should not be shown. This means that, if creating the query visually, you must clear its Show check box. Here is an example:
If you are writing a SQL statement for the query, you must not list this additional column in the SELECT expression:
```
SELECT Students.Gender,
       Count(Students.Gender) AS CountOfGender,
       (Count([Gender])/164)*100 AS Percentage
FROM Students
WHERE (((Students.Gender) Is Not Null))
GROUP BY Students.Gender;
```
This would produce:

Practical Learning: Summarizing

In the bottom section of the window, change the header of the second column to Qty: Category
Click its Total combo box and select Count
Change the header of the third column to Total Spent: Purchase Price
Click its Total combo box and select Sum
Change the header of the fourth column to Average: Purchase Price
Click its Total combo box and select Avg

Right-click the query and click SQL View

SELECT 	[Company Assets].[Asset Type], 
	Count([Company Assets].[Asset Type]) AS Qty, 
	Sum([Company Assets].[Purchase Price]) AS [Total Spent], 
	Avg([Company Assets].[Purchase Price]) AS Average
FROM [Company Assets]
GROUP BY [Company Assets].[Asset Type];

Right-click the title bar and click Datasheet View
Close the query
When asked whether you want to save, click No
Open the Altair Realtors2 database
On the Ribbon, click Create
In the Queries section, click Query Design
In the Show Tables dialog box, double-click Properties and click Close
On the Ribbon, click the Totals button
In the list of fields, double-click Property Type, Market Value, Market Value, and Property Type
In the bottom section of the window, change the header of the second column to Cheapest: Market Value
Click its Total combo box and select Min
Change the header of the third column to Most Expensive: Market Value
Click its Total combo box and select Max
For the fourth column, set its Total to Where (clear its Show check box if necessary)
Set its Criteria to Is Not Null and press Enter

Right-click the query and click SQL View

SELECT 	Properties1.[Property Type], 
	Min(Properties1.[Market Value]) AS Cheapest, 
	Max(Properties1.[Market Value]) AS [Most Expensive]
FROM 	Properties1
WHERE 	(((Properties1.[Property Type]) Is Not Null))
	GROUP BY Properties1.[Property Type];

Right-click the title bar and click Datasheet View
Close the query
When asked whether you want to save, click No


Previous	Copyright © 2010-2019, FunctionX	Next