Operations on Data Analysis

The techniques we used in the last three lessons to analyze data consisted of identifying a value in a particular field and using that value as a basis for filtering.

1. From the resources that accompany our lessons, open the Altair Realtors2 database
2. In the Navigation Pane, under Properties1: Table, double-click Properties1 to open it

Filtering By Value

In a typical table, each column would have one or more values. As mentioned in our introduction to sorting, a column may have the same value(s) occurring over and over again while another column may have a different value for each record. When filtering data, you can ask Microsoft Access to include only a certain value or only some of the values of the list. One way you can do this:

• Click a column header or any cell under a column for a table or query, or click a control or its accompanying label on a form. Then, in the Sort & Filter section of the Ribbon, click Filter
• On a table or query, click the down-pointing button on the right side of the name of the column

In both cases, a window would display, containing the values of the column on which the action was performed. Each value has a check box:

In the window that comes up, besides the values of the column, there are the (Select All) and the (Blank) items (the values of the columns in the checked list box are arranged in alphabetical order). To dismiss the window, you can press Esc or click Cancel.

When the window comes up, to select only the one value whose record(s) must be displayed, clear the check boxes of the other items and leave the check box on that one only:

Then click OK. This would display only the records that share the value that had the check box:

In the same way, you can show only empty fields by selecting the (Blanks) option.

To remove the filter, we saw in Lesson 22, that you could click the Toggle Filter button on the Ribbon or the Filtered button on the table, the query, or the form. As an alternative, you can display the window again, click the (Select All) option and click OK.

Practical Learning: Filtering for a Value

1. On the form, click Condition
2. In the Sort & Filter section of the Ribbon, click Filter
3. In the list that appears, clear all check boxes except Excellent
    
   
4. Click OK
5. Navigate to various properties and notice that only those in excellent condition are part of the list

Besides the techniques we saw in Lessons 22 and 23, as an alternative to filtering by value, you can filter by form. To do this, open the table or query in Datasheet View or, or a form in Form View. Then, in the Sort & Filter section of the Ribbon, click Advanced and click Filter By Form. The table, query or form would become empty and all records would get hidden:

To select a value, click the cell under its column header. A combo box would come up. Click the arrow of that combo box to display its values. The list would contain an empty item as the first, followed by the values of the column that was selected:

If you want to show the records that are empty on that column, select the blank first item. Otherwise, select the desired value from the list.

The combo box in a column displays an editable text box. Instead of selecting a value from the list, you can type a Boolean expression. Here is an example:

After making a selection or typing the expression, to apply the filter:

• You can right-click the column header on the table or query, or the control (or its accompanying label) on the form, and click Apply Filter/Sort
• In the Sort & Filter section of the Ribbon, click Advanced and click Apply Filter/Sort

This action would cause the table, query, or form to display the result.

To save the results of data analysis on a table or query, display the Filter By Form format again. Then, right-click the table or query and click Save As Query. You will be prompted for a name.

After filtering, the value (criterion) is stored in memory and you can filter again as many times as you want by clicking the Apply Filter/Sort button on the Ribbon. Otherwise, you can remove the filtering by clicking the Toggle Filter button on the Ribbon.

1. On the Ribbon, click Advanced -> Filter By Form
2. Right-click anywhere on the form and click Clear Grid
3. Click Stories, then click the arrow of the appearing combo box and select 3
    
   
4. Right-click an unoccupied area of the form and click Apply Filter/Sort
5. Navigate to different records. Notice that only the properties that have 3 levels are displayed

To further assist you with sorting and filtering, Microsoft Access is equipped with a grid that resembles a query. Before using it, display the table or query in Datasheet View, or display the form in Form View. Then, in the Sort & Filter section of the Ribbon, click Advanced -> Advanced Filter/Sort... A window with a top and a bottom sections would appear:

To use it, select a column as we learned in Lesson 21. After selecting a column, in the bottom section of the window, in the Criteria box corresponding to the column, type a Boolean expression. Here is an example:

To apply the filter:

• Right-click the window and click Apply Filter/Sort
• In the Sort & Filter section of the Ribbon, click Advanced -> Apply Filter/Sort

To perform another query, you can return to the grid and change your criterion.

After filtering data with a grid, if you want to save the result, right-click the window and click Save As Query. A dialog box would request a name from you.

To remove the previous criterion, right-click the window and click Clear Grid. After using the grid, you can close it and keep the table, query, or form open.

Practical Learning: Filtering Using a Grid

1. On the Ribbon, click Advanced -> Advanced Filter/Sort...
2. Right-click anywhere on the form and click Clear Grid
3. From the top section, drag Bedrooms and drop it in the first left column in the bottom section
4. Under Bedrooms, click Criteria and type >= 4
    
   
5. Right-click an unoccupied area of the window and click Apply Filter/Sort
6. Navigate to different records. Notice that all displayed properties have at least 4 bedrooms
7. Close the form

Introduction

To perform data analysis beyond what we learned so far in terms of the techniques of sorting, data selection, and exclusion, you can use operators. These operators are part of SQL. If you analyze data visually using the techniques we saw in the last two lessons, Microsoft Access applies the operators behind the scenes and you would not be aware of them. In the next few sections and lessons, we will learn how to use some of these operators. The operators can be used on a table, a query, or a form. The operators can also be applied to a SQL statement used as the Record Source of a form or report. The operators can also be included in a SQL expression used as the Control Source or the Row Source of a control on a form or report. You can use an operator to only perform data analysis or to create a query to be saved:

• When performing data analysis, to use the operators on a table, a query, or a form
  • Use Filter By Form and enter the expression that contains the operator in the editable text box(es) under the column(s)
  • Use the grid (Advanced Filter/Sort) and enter the expression in the Criteria box(es) that correspond(s) to the column(s)
• To create a query that would be saved, display it in Design View, and enter the expression in the Criteria box(es) that correspond(s) to the column(s)

When writing the expression, there are a few rules you should observe:

• Each operator must be written as indicated
• To include a letter, a character, or a string, type it between double-quotes. An example is “M”. Another example would be “El Salvador”. In some cases, if you forget to use the double-quotes, Microsoft Access would add them. In some circumstances, if you forget to use the double-quotes, Microsoft Access would not add them and the expression may fail. Therefore, to be on the safe side, always add the quotes yourself
• If a date or time value is part of an expression, include it between two # symbols. An example would be #12/8/94#. Another example is #05/02/2004#. If you forget the # symbol, most of the time, Microsoft Access would not correct it and you may receive an error
• All built-in constants, True, False, NULL, etc, do not use quotes

Of course, there are situations in which these rules can or must be applied differently. When in case, you will be appropriately directly. Remember that nothing is case sensitive.

Using an Alias Name for a Column

If you create a query and display its result, by default, each column displays its caption as the name of the column. If you want, you can display a different caption for any column of your choice. To do this, display the query in Design View. Then, in the Field box that corresponds to the column, on the left side of the column name, type the caption of your choice, followed by :, and followed by the name of the column. Here is an example:

You can use the same technique to create aliases for other columns as you want. Here are examples:

When you execute the query, a column that received an alias would display it as its caption:

In SQL, an alias is created using the AS keyword as in the following formula:

ColumnName AS Alias

The ColumnName factor is the name of the column in the table (or query). The AS keyword is required. The Alias factor is the caption you want the column to display. Here are examples from the above query:

SELECT Properties.[Property #] AS [Prop #],
       Properties.[Property Type],
       Properties.Bedrooms AS Beds,
       Properties.Bathrooms AS Baths,
       Properties.[Year Built],
       Properties.[Market Value]
FROM Properties;

Concatenating the Strings

One of the results you may want to get from data analysis or a query consists of combining two or more columns to display them together as one. For example, when creating a table that would hold information about people, you may decide to keep the first and last names separate, but during data analysis or when creating a query, you may want to combine the first and last names to get a full name.

To concatenate two column names, you can use the + operator. To concatenate more than two strings, enter their names separated by + operators. An example would be: FirstName + " " + LastName. To do this for a query, open it in Design View. In a Field box, enter the concatenating expression. Here is an example:

To create a more complex expression, you can use one or more of the functions we reviewed in Lesson 17 such as the Immediate If function. Instead of just the columns in the query, you can also include normal strings in the operation. Here is an example:

Sex: IIf([Gender]="Male","M","F")

This expression says, "If the the value of the Gender field is Male, display M, otherwise display F". If this expression is entered in the Field box of a query, when the query is run, a column named Sex would display and its values would result from the expression.

Using Arithmetic Operators

An arithmetic operator can be used to create an expression that results in a calculation. For example, you can multiply hourly salaries by the time the employees worked in a day to produce their dai salaries:

You can also use a MOD operator to find out if the value of a column is divisible by a certain number. You can also combine a function and an arithmetic operation to create a good expression.

Negating an Expression

In previous lessons we saw how to select a value to apply to a filter. In this and the next few lessons, we will learn many other operations that evaluate to true or false. The SQL provides an operator that can be used to change the Boolean result of an operation from true to false or from false to true. This operator is called NOT and comes in two flavors. To use it, in most cases, you can precede the expression with NOT or <>.

Imagine you perform filtering by form on a table that includes a column that lists a value as PG-13:

To negate such a filter, you can precede the string with NOT:

The result would include all records that do not include the value that was set on the right side of NOT. Remember that you can also use <> instead of NOT. Here is an example:

Introduction

In our introduction to filtering in the previous lesson, we saw how to select a particular value from a column and get a list of only the records that include that value for that column. We also saw that, using Filter By Form, we could select a value from a combo box under a column header, apply that filter, and get the records that include that value. All these techniques actually allowed us to apply SQL visually. The SQL supports this filtering through an operator named WHERE.

Using WHERE

In SQL, the WHERE operator is used to specify what value, from a column, to apply to a filter. The formula to follow is:

SELECT What FROM WhatObject
WHERE Condition;

From what we learned in Lesson 21, the new operator in this formula is WHERE and it is required. The Condition is formulated as a logical comparison in the form:

FieldName Operator Value

The FieldName is the name of the field on which to apply the filtering. The Operator factor is a Boolean operator. Depending on the operator and your intentions, the Value can be one of the values in the FieldName or it can be a value not necessarily in the field.

There are various ways you can visually use the WHERE operator:

• You can use Filter By Form on a table, a query, or a form. After opening the list, click the combo box under a column and select the desired value
• You can use a grid. Use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, enter the desired value

Besides using WHERE in data analysis, we have already seen that if you want to save the results of data analysis, you should store them in a query. We also saw that you could create a SQL expression and use it as the Record Source of a form or a report. Therefore, after opening a query in Design View or the Query Builder for a form or report, select the desired column(s). In the bottom section, click the Criteria box of the column that holds the values and type the desired value.

Practical Learning: Using WHERE

1. On the Ribbon, click Create and, in the Other section, click Query Design
2. From the Show Tables dialog box, click Properties, click Add, and click Close
3. From the top section of the window, double-click Property #, Date Listed, Property Type, Condition, City, State, Bedrooms, Bathrooms, and Market Value
4. In the bottom section, under Property Type, click the box corresponding to Criteria and type = "Single Family"
    
   
5. Right-click the title bar of the query and click SQL View
    

   SELECT Properties.[Property #], Properties.[Date Listed], Properties.[Property Type], Properties.Condition, Properties.City, Properties.State, Properties.Bedrooms, Properties.Bathrooms, Properties.[Market Value] FROM Properties WHERE (((Properties.[Property Type])="Single Family"));

6. Right-click the title bar of the query and click Datasheet View.
   Notice that only single family homes display

When creating a query that creates a list of records based on a particular value, the presence of the column that holds that value may become redundant. For example, if you create a list of real estate properties that includes only single family homes, and if it is evident to the user that the list includes only those properties, there is no point to still include that column in the view. This means that the column that holds the WHERE condition can be left out of the expression displaying it. This technique also applies to the other SQL operators we will study in this and the next lessons.

To visually remove the column that holds a condition, in the Design View of the query, you can clear its Show check box.

Practical Learning: Hiding a Conditional Column

1. Right-click the title bar of the query and click Design View
2. In the bottom section of the window, under Property Type, clear the Show check box
    
   
3. Right-click the title bar of the query and click SQL View
    

   SELECT Properties.[Property #], Properties.[Date Listed], Properties.Condition, Properties.City, Properties.State, Properties.Bedrooms, Properties.Bathrooms, Properties.[Market Value] FROM Properties WHERE (((Properties.[Property Type])="Single Family"));

4. Right-click the title bar of the query and click Datasheet View
    
   
5. Close the query
6. When asked whether you want to save it, click Yes
7. Type Single Family Homes and click OK

BETWEEN This AND That Value

In the previous two lessons, we saw that you could filter by a value between two other values, in a range. The SQL supports this technique through an operator named BETWEEN. The BETWEEN operator is combined with the AND operator to get a list of records between two values. Its formula is:

WHERE FieldName BETWEEN Value1 AND Value2

The WHERE, BETWEEN, and AND operators are required. The FieldName must be one of the fields on the table. The first value represents the lowest (or highest) value in the range while the second value represents the highest (or lowest) value of the range.

To visually filter BETWEEN:

• To use Filter By Form on a table, a query, or a form, after opening the list, click the combo box under a column and type the BETWEEN Value1 AND Value2 expression
• To use a grid, use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, type the BETWEEN Value1 AND Value2 expression.

If you want to save the list, start a query and display it in Design View. Select the column(s) you want to use. In the Criteria box that corresponds to that column, enter the the BETWEEN Value1 AND Value2 expression. Then save the query.

Practical Learning: Filtering BETWEEN

1. In the Navigation Pane, under Properties: Table, double-click Properties
2. In the Sort & Filter section of the Ribbon, click Advanced -> Filter By Form
3. Just in case, right-click anywhere on the form and click Clear Grid
4. On the form, click Market Value and type
   BETWEEN 350000 AND 450000
5. Right-click the form and click Apply Filter/Sort
6. Navigate the records and notice that only the properties whose price is between 350,000 and 450,000 display
7. Close the form

Introduction

When studying filtering by value, we saw that you could display a list of the values of a certain column, clear the check boxes of the values you do not want, keep the check boxes on the values you want, and apply the criteria. That was a technique of working on a range of values. To support this, the SQL allows you to create a list of values from a column and get only the records that include one of those values. This is done using the IN operator. After the SELECT expression of the statement, the formula to use IN is:

IN(Value1, Value2, Value_n)

The IN operator is followed by parentheses. Inside the parentheses, type each of the desired values from the column on which to apply the filter, separated by commas.

Using IN

To create an IN operation:

• Use Filter By Form on a table, a query, or a form. After opening the list, click the combo box under a column and type the IN expression
• Get the grid, use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, enter the IN expression. Here is an example:
   
  

If you want to save the list, start a query and display it in Design View. Select the column(s) you want to use. In the Criteria box that corresponds to that column, enter the IN expression.

Practical Learning: Using IN

1. In the Navigation Pane, double-click Properties1: Table
2. In the Sort & Filter section of the Ribbon, click Advanced -> Filter By Form
3. Click the box under the City column header and type IN("Silver Spring", "Takoma Park", "Rockville")
    
   
4. Right-click a cell in the table and click Apply Filter/Sort.
   Notice that only properties in those cities are displaying
    
   
5. Close the query
6. When asked whether you want to save, click Yes
7. Type Single Family Homes and click OK

Introduction

In Lesson 22, we saw that when a column uses strings, sometimes you may know an approximation of a string you are looking for, or you may want to filter based on a range of characters. For example, you may not remember the exact first name of a student you are looking for but you think the name ends with "ette", like Jeannette, Bernadette, Pierrette, or Paulette. In another case, you may know only the character by which a name starts, or you may know a substring in the name you are looking for. The SQL supports these types of filtering using an operator named LIKE.

Using LIKE

The LIKE operator uses some special characters or symbols called wildcards. To start:

• To use Filter By Form on a table, a query, or a form, after opening the list, click the combo box under a column and type the LIKE expression
• To use a grid, use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, enter the LIKE expression.

If you want to save the result, start a query in Design View, select the column(s) you want to use. Then, in the Criteria box that corresponds to that column, type the LIKE expression.

To use a LIKE operation in SQL, after the SELECT expression, type LIKE followed by the intended expression. When you apply the filter, all records that abide by the expression would be in the result.

LIKE an Exact String

The simplest way to use LIKE is to apply a string to it, the same way you would use a filter by selection. For example, if you have a list of people and one of them is named "Moore" or some of them are named "Moore" and you want to find the record(s) with that name, you would use LIKE "Moore".

LIKE a Date/Time Value

If the value you want to find is a date, include it between two # signs as in LIKE #8/8/1993#.

LIKE Multiple Characters

The most regular use of the LIKE is with strings. To get a list of strings that include any characters as a substring in a string, use the * symbol. For example, if you do not know how the string starts but you know how it ends, type LIKE "*". On the right side of *, type the substring that the string should end with. Here is an example:

If you know how the string starts but do not know how it ends, precede the substring with *. An example would be LIKE "jean*". This would produce records that have a string that starts with jean regardless of how it ends. Examples would be Jean, Jeannette, Jeanot.

If you do not know how the string starts or how it ends but know one or more characters that it includes, surround the substring with * symbols. Here is an example:

You may want to use a range of characters to filter by. For example, imagine you want to find the strings that start with characters in the range from d to g. To specify the range, use [] as a placeholder for the range. Inside the square brackets, type the first character of the desired range, followed by -, and followed by the last character of the desired range. Outside the [] placeholder, use the * character to specify that the rest would precede, would follow, or both. For example, to specify that you want the strings that start with any character from o to r followed by anything, you would use LIKE "[o-r]*":

In the same way, as stated already, you can use the * to the left, to the right or both. Also, you can combine the [] placeholder with the other wildcards.

Instead of using a range of characters, you may want to exclude the characters of a specific range. To negate the filter of a range of characters, you can precede the LIKE operation with the NOT operator. An example would be NOT LIKE "[c-w]*":

As an alternative, you can start the inside of the [] placeholder with !. An example would be LIKE "[!o-r]*". This would result in all records that do not start with o, p, or r.

Instead of finding a range of characters, you may want to use specific characters. To do this, use the [] as the placeholder. Inside the square brackets, enter the characters separated by commas. An example would be LIKE "[a, h, o, y]*":

To negate the filter, you can precede the LIKE operation with the NOT operator. An example would be NOT LIKE "[a, h, o, y]*". As an alternative, inside the [] placeholder, start with the ! symbol. An example would be LIKE "[!a, h, o, y]*". In both cases, the list would include records that do not start with a, h, o, or y.

LIKE a Single Character

The * character on a LIKE operation is used to find many characters. If you want to find only one character, use the ? wildcard. For example, if you do not know by what character a string starts but know one or more characters after it, you can use ? followed by a substring. An example would be LIKE "?ane". This would produce such strings as Pane, Bane, or Sane.

If you do not know by what character a string starts, know the second character, but do not remember the rest of the characters, you can combine the ? and the * symbols. An example would be LIKE "?i*":

Lesson Summary

 

MCAS: Using Microsoft Office Access 2007 Topics

P2	Filter data

 

Exercises  

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open the Employees table
3. Find the employees whose last name start with k, m, or y
    
   
4. Open the Customers form
5. Use Filter By Form to show a list of customers whose last name start with B (use LIKE; the B letter is preceded by an empty character and b is followed by anything)
    
   
    
   
    
   
6. Use Filter By Form to find the customers whose area code is 301 (use LIKE)

Watts A Loan

1. Open the Watts A Loan database
2. In the Customers form, find the customers whose middle name has two letter L
    
3. Find the customers whose last name include the letter n
    

World Statistics

1. Open the World Statistics1 database
2. Open the Countries form and show only the countries whose common name ends with ia
3. Open the Countries form and show the list of countries that are considered a republic (the government type includes the word Republic, even if there is another word, such as Islamic Republic)
4. Do not save anything

US Senate

1. Open the US Senate1 database
2. Open the Senators table and show a list of senators from Nebraska, California, and Ohio (use Filter By Form and create an IN expression that includes the 2-letter names of the states)
3. Open the Senators form and show the senators whose name include the substring ei
4. Do not save anything