Home

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):

If you have a column named Gender and that displays the genders as Male and Female but you want to display only M or F respectively, you can use the Left() function in an expression as LEFT(Gender, 1)

Function

In the same way, you can use any of the functions we have seen so far.

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:

On a list of students that includes their dates of birth in a column named DOB, to get the list of students born in 1992, you would set the condition as Year([DOB])=1992

Function

The Domain-Based Functions

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:

FunctionName(WhatValue, FromWhatObject, WhatCriteria)

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.

Summary Queries

 

Introduction

Consider the following list of students:

Students

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.

Practical Learning: Introducing Summary Queries

  1. Start Microsoft Access
  2. From the resources that accompany our lessons, open the Bethesda Car Rental1 database
  3. On the Ribbon, click Create and, in the Queries section, click Query Design
  4. In the Show Tables dialog box, click Company Assets, click Add, and click Close
  5. In the top section of the window, double-click Category
  6. Double-click Category again
  7. Double-click Purchase Price
  8. Double-click Purchase Price again

Creating a Summary Query

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:

  • In the Show/Hide section of the Ribbon, click the Totals button Totals
  • Right-click the bottom section of the query window and click Totals

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:

I you to get the number of students by gender from a list of students, it is normal that you be interested only in the column that holds that information

When the results come up, they would be grouped by their categories:

When the results come up, they would be grouped by their categories

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:

The purpose of a summary query is to provide some statistic. 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:

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

Practical Learning: Creating a Summary Query

  • In the bottom section of the window, right-click a cell and click Totals
 
   
 

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
     
    Summary Query
    SELECT Students.Gender, Count(Students.Gender) AS CountOfGender
    FROM Students
    GROUP BY Students.Gender;
    Summary Query
  • First: Microsoft Access uses the First() function to get the first occurrence of the value in the category
     
    Summary Query
     
    Summary Query
  • 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:
     
    Summary Query
     
    Consider the following query that wants to summarize the time worked:
     
    Summary Query
     
    Summary Query

    If the column holds numeric values:
    • Sum: The Sum() function is used to sum up the values in the category
       

      Summary Query

      SELECT TimeKeepers.EmployeeNumber,
      Sum(TimeKeepers.TimeWorked) AS SumOfTimeWorked
      FROM TimeKeepers
      GROUP BY TimeKeepers.EmployeeNumber;

      Summary Query

    • 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;

      Summary Query

    • Min: The lowest value of the category would be produced from the Min() function

      Summary Query

      The result is the lowest time worked for each employee:

      Summary Query

    • Max: The highest value of the category would be produced using the Max() function. Here is an example:
       

      Summary Query

      SELECT TimeKeepers.EmployeeNumber,
      Min(TimeKeepers.TimeWorked) AS MinOfTimeWorked,
      Max(TimeKeepers.TimeWorked) AS MaxOfTimeWorked
      FROM TimeKeepers
      GROUP BY TimeKeepers.EmployeeNumber;

      Summary Query

    • 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:
       

      Summary Query

      SELECT TimeKeepers.EmployeeNumber,
      StDev(TimeKeepers.TimeWorked) AS StDevOfTimeWorked
      FROM TimeKeepers
      GROUP BY TimeKeepers.EmployeeNumber;
      

      Summary Query

      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;
      

      Summary Query

      The statistical variance of the values in the category would be calculated

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

    Summary Query
     

    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:  
     

    Summary Query

    SELECT Students.Gender,
    Count(Students.Gender) AS CountOfGender,
    (Count([Gender])/164)*100 AS Percentage
    FROM Students
    GROUP BY Students.Gender;

    Summary Query

  • 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:
     

    Summary Query

    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:

    Summary Query

Practical Learning: Summarizing

  1. In the bottom section of the window, change the header of the second column to Qty: Category
  2. Click its Total combo box and select Count
  3. Change the header of the third column to Total Spent: Purchase Price
  4. Click its Total combo box and select Sum
  5. Change the header of the fourth column to Average: Purchase Price
  6. Click its Total combo box and select Avg
     
    Query
  7. 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];
  8. Right-click the title bar and click Datasheet View
     
    Query
  9. Close the query
  10. When asked whether you want to save, click No
  11. Open the Altair Realtors2 database
  12. On the Ribbon, click Create
  13. In the Queries section, click Query Design
  14. In the Show Tables dialog box, double-click Properties and click Close
  15. On the Ribbon, click the Totals button Totals
  16. In the list of fields, double-click Property Type, Market Value, Market Value, and Property Type
  17. In the bottom section of the window, change the header of the second column to Cheapest: Market Value
  18. Click its Total combo box and select Min
  19. Change the header of the third column to Most Expensive: Market Value
  20. Click its Total combo box and select Max
  21. For the fourth column, set its Total to Where (clear its Show check box if necessary)
  22. Set its Criteria to Is Not Null and press Enter
     
    Summary
  23. 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];
  24. Right-click the title bar and click Datasheet View
     
    Summary
  25. Close the query
  26. When asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Next