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
3. In the Queries section, click Query Design
4. On the Show Table dialog box, click Properties, click Add and click Close
5. In the list of properties, double-click Property #, Property Type, City, State, Year Built, and Market Value
6. 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;

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

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 Ascending . 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.

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.

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 Remove Sort

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 Remove Sort
4. To see the list of properties whose type is not known, right-click Property Type and click Sort A To Z
5. On the Ribbon, click Remove Sort

Filtering Empty Fields

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.

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 an empty cell under the column header. 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 an empty cell in that column and click Does Not Equal Blank
  • Click any empty cell under the column header. 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.

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 that 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

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

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.

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 the Form View or the 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.

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
2. In the Queries 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, and Condition
   

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

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