Queries-Based Functions

Queries and Built-In Functions

In previous lessons, we saw that Microsoft Access was equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

There are two primary ways you can include a function in a query. If you are visually building the query in the Select Query window, you can write an expression that includes the function. Consider the following list of employees:

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName]

The equivalent SQL statement is:

SELECT Employees.EmployeeID, 
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM Employees;

This would produce:

Notice that some employees don't have a middle name in their record and the field includes an extra useless empty space. Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left$ function to retrieve the first character of the middle name and include the call to that function in your query. To do this visually, if you are creating the query in the Select Query window, simply enter the function call where the column name would be. For our scenario, column name would be set as follows:

Employee: [FirstName] & " " & Left([MiddleName],1) & " " & [LastName]

This would produce:

Once again notice that some records don't have a middle initial because they don't have a name. Also, the middle name of some records was entered in lowercase. To convert a string to uppercase, we saw that we could use the UCase$ function. Here is an example:

Employee: [FirstName] & " " & UCase(Left([MiddleName],1)) & " " & [LastName]

For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

Employee: IIf(IsNull([MiddleName]),[FirstName] & " " & 
		[LastName],[FirstName] & " " & UCase(Left([MiddleName],1)) & 
		" " & [LastName])

The equivalent SQL statement is:

SELECT Employees.EmployeeID, IIf(IsNull([MiddleName]),[FirstName] & " " & _
		[LastName],[FirstName] & " " & UCase(Left([MiddleName],1)) & _
		" " & [LastName]) AS Employee
FROM Employees;

This would produce:

In the same way, you can use any of the built-in functions we reviewed in Lesson 14.

As done for a table, data can be entered in a query. Before doing this, you must first create the query. After creating the query, display it in the Datasheet View and enter the values in the cells. Only the columns that come from a table (or an existing query) can receive values. A column that was created from an expression cannot be edited since the database engine takes care of updating it using the entered data.

When data is entered into a query, its original table is automatically updated. This means that you can create new records intended for a table but using a query. 

Summary Queries and SQL Aggregate Functions

 

Introduction

Besides the various procedures built-in the Microsoft Access and the Microsoft Visual Basic libraries, the SQL provides an additional collection of functions that you can use to perform various valuable operations in what are referred to as summary queries. A summary query is used to perform a common operation on the values held by a query.

Imagine that you are creating a database for a furniture stores where the job of sales people are evaluate based on the number, frequency of sales, or total sales they have performed during a set period such as a day, a week, a month, a trimester, a semester, a year, etc. A regular query would not allow you to easily get the totals of their sales. Instead, you can create a summary query to easily perform such an operation.

Consider the following table:

There are various re-occurring pieces of information in various columns. There is also a column that displays monetary values. These different columns can be used to perform database analysis by grouping the values in logical manners. This query is used to create groups of values. You can start the SQL statement with SELECT as we have seen so far. Here is an example:

SELECT Videos.CopyrightYear
FROM Videos;

This query would contain only the CopyrightYear column. When creating a summary query, you must consider grouping the values in a certain manner. A group can be made of names of columns or made of results of functions or expressions. You should have a column that would be used as the reference. This is the column on which the "summary" would be performed. To indicate this reason column, at the end of the SQL statement, precede its name with the GROUP BY expression. Here is an example:

SELECT Videos.CopyrightYear
FROM Videos
GROUP BY Videos.CopyrightYear;

To actually perform the necessary operation(s), a query uses a series of functions referred to as aggregate. To make it  easy to create this type of query, you can use either the Query Wizard or in Design View. 

To create a summary query using the wizard, start the Query Wizard as you would normally. In the first page of the wizard, you should select columns whose values follow a pattern. From the above table, imagine that you want to get a list of videos per year but instead of showing all videos for each year, you want to know how much each year of the movies costs you. To create this type of query using the wizard, you can start by double-clicking Create Query By Using Wizard. In the first page of the wizard, you should select the column that has a series of re-occurring values. In this case, these would be CopyrightYear and PriceBought:

In the second page of the wizard, you would be asked to specify whether to create a regular or a summary query. If you want to create a summary query, you would click the Summary radio button and click the Summary Options button:

This would open the Summary Options dialog box. From there, you can click one or more check boxes:

After using the Summary Options dialog box, you can click OK, continue with the wizard, give it a name and display the result. Like a regular query, you can also modify a summary query that was creating from the wizard. For example, you can set a criterion:

This would produce:

Notice that, in the © Year column, each year appears only once. The Count Of Videos column displays the number of videos our table includes for the corresponding year.

To set a condition in a summary query, you precede the condition with the HAVING operator. The SQL statement of this query is:

SELECT DISTINCTROW Videos.CopyrightYear,
		   Sum(Videos.PriceBought) AS [Sum Of PriceBought],
		   Count(*) AS [Count Of Videos]
FROM Videos
GROUP BY Videos.CopyrightYear
HAVING (((Videos.CopyrightYear) Is Not Null));

One of the requirements of the Simple Query Wizard is that it must be able to recognize re-occurring values in the column(s) you selected. Even if you select a column with re-occurring values, if you select only one column, the second page of the wizard would not give you the option to create a summary query. Another limitation is that, in the first page of the wizard, after selecting a column, it is removed from the Available Fields list. What if you want to use a column twice in your summary query? The wizard doesn't allow this. In fact, that is why we selected the PriceBought column in the above query. Based on the result we wanted to get, our query did not need it. Even if you select more than one column, for example, if you are creating a query from our Videos table where you select Title and CopyrightYear, if the wizard doesn't recognize patterns, the second page of the wizard will not give you to option to create a summary query.

Instead of using the wizard, you can create a summary query in the Design View. As mentioned above, you start a summary query like a regular query. To make it a summary query, you can click the Totals button on the Query Design toolbar. This would add a new row labeled Total in the lower section of the window and each selected column would be assigned the value Group By:

As mentioned earlier, as opposed to the Simple Query Wizard, in the Design View of a query, you can add a column more than once. For a summary query, if you want to use only one column, for example to count the occurrences of its values, you can add it a second time.

In the Total cell corresponding to the desired column, you can click the arrow of the combo box and select the desired option:

This would produce:

Notice that, this time, we can get the number of videos per year from our Videos table. The SQL statement of this query is:

SELECT Videos.CopyrightYear, Count(Videos.CopyrightYear) AS CountOfCopyrightYear
FROM Videos
GROUP BY Videos.CopyrightYear
HAVING (((Videos.CopyrightYear) Is Not Null));

To perform its various operations, a summary relies in what are referred to as aggregate functions:

• Count: The Count() function is used to count the number of occurrences of a value in a range. Here is an example:
   

  SELECT Videos.Rating, Count(Videos.Rating) AS CountOfRating FROM Videos GROUP BY Videos.Rating;

• Sum: The Sum() function is used to create a total of numeric values of common entries in a column. Of course the values must be numeric. Here is an example:
   

  SELECT Videos.CopyrightYear, Sum(Videos.PriceBought) AS SumOfPriceBought FROM Videos GROUP BY Videos.CopyrightYear;

• Avg: The Avg() function is used to calculate the average of numeric values of a group. Here is an example:
   

  SELECT Videos.CopyrightYear, Avg(Videos.PriceBought) AS AvgOfPriceBought FROM Videos GROUP BY Videos.CopyrightYear;

• Min and Max: The Min() (or the Max()) function is used to calculate the minimum (or the maximum) of a group of values. If there is only one value in the group, both functions return the same value. Here is an example:
   

  SELECT Videos.CopyrightYear, Min(Videos.PriceBought) AS MinOfPriceBought, Max(Videos.PriceBought) AS MaxOfPriceBought FROM Videos GROUP BY Videos.CopyrightYear;

• First and Last: The First() (or the Last()) function is used to get the first (or the last) value in a range. If there is only one value in the group, both functions return the same value.
• StDev: 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 Videos.CopyrightYear, StDev(Videos.PriceBought) AS StDevOfPriceBought FROM Videos GROUP BY Videos.CopyrightYear;

• 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 Videos.CopyrightYear, Var(Videos.PriceBought) AS VarOfPriceBought FROM Videos GROUP BY Videos.CopyrightYear;

• Expression: Consider the following query that we saw earlier:
   
  

  Imagine that you want to get the percentage of occurrences of each rating. To get such a value, you can write an expression such as Count([Videos].[Rating])/20. If you want to do this in a summary query, you can specify the Total type as Expression. Here is an example:  
   

  SELECT Videos.Rating, Count(Videos.Rating) AS CountOfRating, Count([Videos].[Rating])/20 AS Percentage FROM Videos GROUP BY Videos.Rating;

• Where: Notice that the above query has an empty (or null) record. This makes that record quite useless for the query. We mentioned earlier that you could set a condition in the Criteria box of the column used as the reference, which in this case would be Rating. 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 Videos.Rating, Count(Videos.Rating) AS CountOfRating, Count([Videos].[Rating])/20 AS Percentage FROM Videos WHERE (((Videos.Rating) Is Not Null)) GROUP BY Videos.Rating;

  This would produce: