Sorting and Filtering Text-Based Fields

Sorting Records on Empty Fields

Introduction

In the previous lesson, we saw how to create queries to isolate or select a list of columns from a table or another query and we saw that data analysis could be performed on queries. In reality, queries are not the only objects used to analyze data. The main reason for using a query is that it can save its results to be used when necessary.

The analysis done on a table or a form is usually temporary and may be lost when you close the table or the form. Other than that, most data analysis operations you can perform on a query are also available on tables and forms.

Data analysis can be performed on tables, queries, and/or forms. To analyze data, display the table or query in Datasheet View, or the form in Form View. To assist you with data analysis, when a table, a query, or a form is displaying in Datasheet View, the Ribbon is equipped with a section titled Sort & Filter in the Home tab:

Also, when a table or a query displays in Datasheet View, each column header displays a down pointing arrow button on its right side:

There are two main aspects involved with data analysis: what you provide to the users and what the users may decide to do with data. In some cases, you may want to create objects such as forms and/or reports that isolate records instead of showing all records. As we saw in the previous lesson, these types of forms and reports can be based on a query or a SQL statement. In some other cases, you can create queries that your users would run to get a fixed list of records based on a rule of your choice. For these and many other reasons, you should be aware of what your users can do and what they should not do.

Practical Learning: Introducing Sorting Records

1. From the resources that accompany our lessons, open the Altair Realtors2 database
2. To create a query, on the Ribbon, click Create and, in the Other section, click Query Design
3. On the Show Table dialog box, click Properties, click Add and click Close
4. In the list of properties, double-click Property #, Property Type, City, State, Year Built, and Market Value
5. To see its SQL code, right-click its title bar and click SQL View
   

   SELECT  Properties.[Property #], 
   	Properties.[Property Type], 
   	Properties.City, 
   	Properties.State, 
   	Properties.[Year Built], 
   	Properties.[Market Value]
   FROM Properties;

6. Right-click its title bar and click Datasheet View

Record Sorting on Empty Fields

Sorting records consists of rearranging them in alphabetical, incremental, chronological, or Boolean order. Sorting can be performed on fields that have numbers, strings, date, time, or Boolean values. Fields can also not display any value but must be sorted also.

When performing data entry, a user may be handed a paper-based list of records, a user may receive the information over the phone, or the database system may receive values electronically. In some cases, the needed values are available. In some cases, some values may be missing. When introducing data entry, we saw that the user could leave those fields empty if the table allows it. As a result, a table, query, or form may end up displaying empty fields. These are also referred to a null field or null values. In reality, or behind the scenes as far as an application is concerned, there is a difference between a null field and an empty field.

A null field is one that has never received any value previously. For example, during data entry, if the user encounters a field for the first time, the field not having a value, and the user skips it, the field is considered null. On the other hand, if the user comes to a field, enters a value in it, then deletes that value (at that same time or at another time), the field is considered empty. This means that the field has had a value before but that value has been lost, deleted, or removed. To make our discussion easier in these lessons, we will interchangeably use the expressions "null fields" and "empty fields" to mean the exact same thing.

When sorting records in alphabetical, incremental, or chronological order, the empty fields always come first. This would allow you to find out which fields have not been filled; that is, what records are incomplete or missing. For this reason, you can sort records on a field, not because you want to get the alphabetical order, but because you would like to find out what record(s) need(s) to be completed; that is, what records are empty.

To sort records, you must specify the field that would be used as the reference. To do this, on the table or query, click a field under the column of your choice. To arrange the list in alphabetical, incremental, chronological, or Boolean order, on the table or query, click the column header or a field under the column. Then, in the Sort & Filter section of the Ribbon, click the Ascending button . You can also right-click to sort the list.

Besides the regular arrangement of records, you can also sort records in reverse alphabetical, incremental, or chronological. When you do this, the empty fields will always come last in the list.

When you have finished sorting the records, it is sometimes important to reset the table before continuing unless you want to keep the list sorted.

When you have sorted the values of a column, the down-pointing arrow button on the right side of the column header becomes equipped with an arrow:

You can keep the list sorted while you are performing other operations. It would remain sorted until you decide to put it back to the way it was previously.

Record Sorting on Forms

In Lesson 9, we saw that a form could display in Datasheet View, like a table or a query. With that type of form, you can apply the same techniques you use to sort records for a table or a query. If you display a form in its regular and most usual format, where it displays one record at a time, you can still perform the same sorting operations as done on a table. The main difference is that, in Form View, the form shows only one record at a time. We also saw that a tabular form resembled a datasheet except that each field is in its own control and the tabular form displays as many controls as its vertical size allows.

When you sort the records in incremental, alphabetical, or chronological order, the empty records would come first. This means that, in the Form View, the first record with the empty field on the control you selected would come first.

Removing the Sorting

In most cases, you should "unsort" a list before continuing. To put the list back in the sequence it previously had:

• In the Sort & Filter section of the Ribbon, click the Clear All Sorts button
• Right-click the query or form and click Clear All Sorts

Practical Learning: Sorting Records

1. To view the list of properties by state starting with those whose state is not known, click the State column header
2. In the Sort & Filter section of the ribbon, click the Ascending button
    
   
3. On the Ribbon, click Clear All Sorts
4. To see the list of properties whose type is not know, right-click Property Type and click Sort A To Z
5. On the Ribbon, click Clear All Sorts

Filtering Empty Fields

Introduction

So far, we were just selecting the necessary fields for our data analysis. In some circumstances, you may want to set a limit on the number of records to display or make available to the user. To do this, you must create a rule and ask Microsoft Access to apply it to a set of records. The rule works like a funnel that decides what to let through and what to retain. The rule is also called a criterion. For example, you can set a criterion that asks a query to consider the list of all students in a school but to restrict the list only to female students.

A filter is a criterion or a set of criteria that must be applied to a set of records to create a list of records that abide by a common rule. Filters can be used to isolate records on a table, a query, a form, or a report. There are differences on the way each type of value handles it.

Filtering

If you have a list of records where fields are empty in a certain column, you can filter the list so that you would get the empty fields only, or you can create a filter that would produce the non-empty fields only.

To filter records that display on a data sheet, first decide what column to use. Then:

• To get a list of records where the fields are empty, you can:
  • Right-click the column and click Equals Blank
  • Click any value under the column. In the Sort & Filter section of the Ribbon, click Selection and click Equals Blank

After clicking, the table or query would display only the records that are empty.

• To get a list of records where the fields are not empty, you can:
  • Right-click the column and click Does Not Equal Blank
  • Click any value under the column. In the Sort & Filter section of the Ribbon, click Selection and click Does Not Equal Blank

After clicking, the table or query would display only the records that are not empty for that particular column.

Removing a Filter

When a table, a query, or a form is filtered, in the Sort & Filter section of the Ribbon, the Toggle Filter button is highlighted . Also, the bottom section of the table, query, or form displays a Filtered button . If you perform another filter on the list, only the selected records would be considered. This means you must decide whether you want the new filtering to apply to all records or only to the new ones. If you want to use all records of the list, you must first remove the previous filter.

To dismiss the previous filtering operation:

• In the Sort & Filter section of the Ribbon, if the window is large enough to show the Toggle Filter button , then click it. If the width used by Microsoft Access is not large enough, then the Sort & Filter section would be equipped with the Remove Filter button . To remove the filtering, you can click that button
• Right-click the table, query, or form and click Clear Filter From ...
• Click the down-pointing button on the right side of the column name and click the Clear Filter From option. Here is an example:
   
  
• In the bottom section of the table, query, or form, click the Filtered button

Practical Learning: Removing a Filter

1. To see the list of properties whose type is not known, on the query, right-click an empty cell under Property Type and click Equals Blank
2. In the Sort & Filter section of the Ribbon, click Toggle Filter
3. To see the list of only the properties that have a property number, on the query, click an empty cell under Property #
4. In the Sort & Filter section of the Ribbon, click Selection -> Does Not Equal Blank
    
   
5. Close the query
6. When asked whether you want to save, click No

Saving Data Analysis Results

In this and the next lessons, we will see different ways to examine the values stored in a database. Every time you finish visually analyzing data, you can dismiss the result or save it. If you do not save the result, all the analysis will be lost. The best way to save the results of data analysis is by saving them in a query. In the previous lesson, we saw that the advantage of using a query is that it could be saved and its data reviewed over and over again. Besides the ability to save sorted and/or filtered lists, queries provide advanced techniques of performing data analysis. Because they use the SQL, they use a syntax that is not directly available to tables, forms, and reports. In fact, queries provide a good alternative to creating the record source that can be used to populate forms and reports.

Sorting String-Based Fields

Introduction

Most fields of a database contain strings. This allows you to sort them in alphabetical order. When sorting the strings of a column, Microsoft Access refers to the language of the computer set in the Control Panel. That language defines its alphabetical rules. This also implies that the rules in one language are not necessarily the same in another language.

To sort the records of a table or query, you must specify the field that would be used as the reference. To do this, on the table or query, click a field under the column of your choice. To arrange the list in alphabetical order, click the column header or a field under the column.

Visually Sorting String Records

Consider the following table:

When many records have the same value for the Property Type column, you can ask Microsoft Access to sort the records based on the type of property. In this case, as seen previously, the empty records would come first. Then, the records that have Condominium as value would display, followed by the records that have Single Family, and so on. When sorting the records, the table, query, or form must still keep each record complete. Therefore, after displaying the field in the order, its corresponding fields are displayed in their equivalent columns. In this case, the property number, the condition, the city, and the state corresponding to the property type would display on the same row with the property type. For example, notice the property whose type is Condominium, its property number being 200417, its condition being Excellent, its city being Germantown, and its state being MD. When the records have been sorted alphabetically, the whole record is kept the same, just at a different position:

To sort the records, identify and click the column header, a cell under a column, a control, or its label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button .

You can also right-click to sort the list:

• Click the down pointing arrow button on the right side of the name of the column and click Sort A to Z
• Right-click the column header or a field under the column and click Sort A to Z

Besides the regular arrangement of records, you can also sort records in reverse alphabetical order. To do this, click the column header or a field under the column header. Then, in the Sort & Filter section of the Ribbon, click the Descending button .

You can also right-click to sort the list in reverse order:

• Click the down pointing arrow button on the right side of the name of the column and click Sort Z to A
• Right-click the column header or a field under the column and click Sort Z to A

When you have finished sorting, it is sometimes important to reset the table before continuing unless you want to keep the list sorted.

To sort records on a form in Form View or Tabular View, click the control or its accompanying label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button .

As mentioned for the table or query, to arrange the strings in alphabetical order, right-click the control or its label and click Sort A to Z.

To sort the records in reverse alphabetical order, click the control or its label and, in the Sort & Filter section of the Ribbon, click the Descending button .

You can also right-click. To arrange the list in reverse alphabetical order, right-click the control or its label and click Sort Z to A.

Sorting Records in SQL

In SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The formula to follow would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The field used as the basis should be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named StaffMembers. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

SELECT FirstName, LastName FROM Employees ORDER BY LastName;

If you use the * operator to include all fields, you can order the list based on any of the table's fields. Imagine that you have created a query that includes all fields. The following statement would list the records of the Employees table in alphabetical order based on the LastName column:

SELECT * FROM Employees ORDER BY LastName;

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM Employees ORDER BY LastName ASC;

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:

SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM Employees ORDER BY LastName DESC;

Practical Learning: Introducing Sorting Records

1. To create a query, on the Ribbon, click Create and, in the Other section, click Query Design
2. On the Show Table dialog box, click Properties, click Add and click Close
3. In the list of properties, double-click Property #, Property Type, City, State, and Condition
   

   SELECT 	Properties.[Property #],
   	Properties.[Property Type], 
   	Properties.City, 
   	Properties.State, 
   	Properties.Condition
   FROM Properties;

4. Right-click its title bar and click Datasheet View
5. To view the list of properties by state in alphabetical order, click any field under the State column
6. In the Sort & Filter section of the ribbon, click the Ascending button
    
   
7. On the Ribbon, click Clear All Sorts

Filtering String-Based Fields

Introduction

Imagine you have a table where some records can be recognized as belonging to a category. For example, in a list of persons, you may have a column that shows each one's gender. Obviously different people would have different values but a group of people would have the same value and another group would have another same value. Based on such a field, you can create a list that includes only the people who share one of the values. To do this, you would filter the values. Microsoft Access provides various techniques to visually filter records.

To filter records that display in a data sheet, first decide what column holds the value you want to filter by. Then:

• To get a list of all records that hold the exact same string as that of your column, you can:
  • Right-click the value in the column and click the Equals option. Here is an example:
     
    
  • Click the value. In the Sort & Filter section of the Ribbon, click Selection and click the Equals option

After clicking, the table or query would display only the records that share the value in the field:

Practical Learning: Filtering Records

1. To see only the single family homes, on the query, right-click Single Family and click Equals "Single Family"
    
   
2. Right-click Single Family again click title bar and click Clear Filter From Property Type

Filtering Records by a Substring

If you have a set of records with a string-based column, you may want to create a list of records that share a portion of a string, also called a substring. Microsoft Access allows you to create a list based on strings that start with a certain substring, that end with a certain substring, or that include a certain substring. To start, first select the substring. Then:

• You can right-click the selected substring and click the Contains option. Here is an example:
   
  
• In the Sort & Filter section of the ribbon, you can click Selection, and click the Contains option

When using the Contains option, you must first select a string or a substring. In some cases, you may want the string to contain one or more characters or substrings. To exercise an advanced level of control on the substring or the combination of (sub) strings, right-click any field under the column, position the mouse on Text Filters and choose one of the options. The Custom Filter dialog box would come up:

When using the Custom Filter dialog box, you can type an exact string or use some characters to create an approximate string.

If you had selected:

• Equals...: You can type the exact string or an approximate string to look for:
  • If you type an exact string, this would be the same as the Equals option we saw above except that the dialog box allows you to enter a string other than the one you would have selected from the column. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string, not part of it. For example if you type Marshall, only records that have exactly Marshall in the column would be part of the result
  • If you do not know the beginning of a string, you can precede a character or a substring with the * symbol. For example, if you type *ar*, all entries that include the substring ar would be included in the list. Examples would be Farms, Edwards, Bartlett. Here is an example:
     
    
     
    
• Does Not Equal...: You can type the exact or an approximate string to look for:
  • If you type an exact string, this would be the same as the Does Not Equal option. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string. All entries that have the substring would be excluded from the result. For example if you type Marshall, all records that do not have Marshall would be in the result
  • If you do not know the beginning of a string, you can precede a character or a substring with the * symbol. For example, if you type *ar*, all entries that include the substring ar would be excluded from the list

• Begins With...: You can type one or a few characters that the content of the values in the column should start with:
  • If you type just one character, such as c, you would get all entries that start with that character, such as Charles, Castro, or Cyrano. Using only one character could result in a too long list. Therefore, you should include as many characters as possible to get fewer results
  • You can also specify a range of characters to start the string. To specify this, you use the []. In [], type the range of alphabetical characters. For example, if you type [dh], the result would include all entries that start with d, e, f, g, or h. Examples would be: Davis, Howerson, or Hoak. Here is an example:
     
    
     
    
  • You can also specify that you want only the entries that start with specific characters. To do this, use []. In [], type the characters separated by commas. An example would be [m,c,p]. This would produce only the records where the entries start with m, c, or p, such as Cole, Miller, Pacheco, Chance, or Porter. Here is an example:
     
    
• Does No Begin With...: You can type one or a few characters that the values in the column should not start with. This would produce the opposite results of the Begins With option
• Contains...: You can type a string or an approximation to look for:
  • If you use an exact string, only the records that exactly include that string would be in the resulting list
  • If you do not know the exact string but know what substring it contains, you can use the asterisk to provide it. For example, if you provide *ar*, all entries that contain ar would be included in the result
     
    
  • You can also provide a list of OR characters that the string should contain. That is, you can ask Microsoft Access to find strings that contain this character, or that character, or that character, and so on. To do this, use [] and, in [], type the characters. An example would be [dgu]. This would produce all strings that include d, all strings that include g, and all strings that include u. Examples would be Arthur, Raymond, Bouba, Gabrielle, Maurice, or Orlando. Here is an example:
     
    
• Does Not Contain...: You can type a string to look for. Any entry that includes the string would be excluded from the list
• Ends With...: You can type one or a few characters that the content of the values in the column should end with:
  • If you type just one character, such as e, you would get all entries that end with that character, such as Jeannette, Pete, Catherine, or Maurice
  • You can also provide an approximation of the characters or substrings to end with
• Does No End With...: You can type one or a few characters that the values in the column must not end with

Filtering By Exclusion

As opposed to filtering records on a value, you can filter the records that do not follow, or are opposed to, a certain rule. This is referred to as filtering by exclusion. To filter the records that are not conform to a criterion:

• Right-click the value of the column and click the Does Not Equal option
• Click the value. Then, in the Sort & Filter section of the Ribbon, click Selection and click the Does Not Equals option

After clicking, the table or query would display the records that do not follow the specified criterion.

You can also filter excluding a substring. That is, you can get a list of records that do not have a certain substring. To do this, first select the substring. Then: 

• You can right-click the selected substring and click the Does Not Contain option
• In the Sort & Filter section of the ribbon, you can click Selection, and click the Does Not Contain option

Practical Learning:  Filtering Records By Exclusion

1. To get a list of properties other than those in Maryland, on the query, right-click MD and click Does Not Equal "MD"
    
   
2. Right-click Single Family again click title bar and click Clear Filter From Property Type
3. Close the query
4. When asked whether you want to save, click No

MCAS: Using Microsoft Office Access 2007 Topics

Exercises

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open the Customers table and sort records by State then remove the criteria
   View a list of customers who live in MD
   View a list of customers who live outside of MD
   Based on the ZIP Code, view a list of customers who live in DC (ZIP Code between 20000 and 20599). Notice some discrepancies of bad data entry
   Close the table without saving it
3. Open the Employees form
   View a list of only employees who have the capacity to create a new bank account
   View a list of cashiers only (including the head cashier)
   View a list of employees who live outside of Baltimore
   View a list of employees who earn less than $16.00/hr
   Close the form

World Statistics

1. Open the World Statistics1 database
2. Open the Countries table and show the list of countries that start with Ca
3. Show the Countries that are a republic

US Senate

1. Open the US Senate1 database
2. Open the Senators form
3. Make the form show only female senators
4. Make the form show only senators from CA