Home

Sorting and Filtering Numbers
 

Sorting Numeric Fields

Numbers provide support for counting in a database and there are different types of numbers, integers and floating-point. One of the particularities of numbers in data entry is that the user types them as they become available. One of the resulting evidences is that, at one time, the numbers in a column may appear disorganized. In some cases this would be just fine. At some other times, you would want to have the values arranged in incremental or reverse incremental orders. The incrementing of a list is the arrangement that goes from the lowest to the highest, such as 0, 1, 2, 3, 4, and so on. The reverse is the arrangement from the highest to the lowest. Microsoft Access provides all the tools necessary to arrange the list in the order of your choice.
 

To sort the values of a column of a table, a query, or a tabular form in incremental order, click the column header or any field under it. Then, in the Sort & Filter section of the Ribbon, click the Ascending button Ascending. Alternatively, you can right-click a column header or any cell under it, and click Sort Smallest to Largest.

As mentioned already, you can also sort the records in reverse incremental order. An example would be 5, 4, 3, 2, 1. To arrange a list of records in reverse incremental order, click the column header or a field under the column. Then, in the Sort & Filter section of the Ribbon, click the Descending button Descending. As an alternative, you can right-click the column header or a field under it, and click Sort Largest to Smallest.

 Data Analysis

As always, when you have finished sorting, you should dismiss the sorting.

You can also sort numeric fields on a form in Form View. Remember that the difference with a Datasheet View is that the form in Form View would display one record at a time. To sort the records of a form in Form View or Tabular View in incremental order, on the form, click the control or its label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button Ascending.

You can also right-click the control or its label and click Sort Smallest to Largest. To get the records in reverse incremental order, right-click the control or its label and click Sort Largest to Smallest.

Sorting Numeric Fields

 

Filtering Records in Numeric Order

If you have columns that display numeric values, you can filter records with criteria that are based on those numbers and the types of numbers they have. You have many options. You can create a list of only records that use one particular value. You can create a list that includes numbers of a certain range. You can create a list that includes numbers lower than a certain value or numbers higher than a certain value.

To filter records that are exactly equal to a certain value:

  • You can right-click the desired value under the column header for a table or a query, or right-click the value in a control or the label of that control for a form, then click the Equals option
  • Click the value on a table, query, or form (for a form, you can also click the label of the control). Then, 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.

Instead of getting the records that use the same value for a column, you may want the records that use values other than a particular one. To create such a list:

  • You can right-click the desired value under the column header for a table or a query, or right-click the value in a control or the label of that control for a form, then click the Does Not Equal option
  • Click the value on a table, query, or form (for a form, you can also click the label of the control). Then, in the Sort & Filter section of the Ribbon, click Selection and click the Does Not Equal option

To use the Equals option, you must click the exact value you want to use as the basis for comparison. Some times, you will need to act on a range of values. To do this, you can right-click any value under a column for a table or query, or a control or its accompanying label on a form, then position the mouse on Number Filters and select from the menu that appears. Here is an example:

Number Filters

As seen for strings, a dialog box would appear. What you do in the Custom Filter dialog box depends on a few factors. If you had selected:

  • Equals...: You can type an exact number or a Boolean expression:
    • You can type a constant number and get the same result as if you had used the Equals option after right-clicking that number in the list
    • You can use a Boolean operator to perform a comparison. This means that you can use any of the comparison operators we reviewed in Lesson 17. For example, to get a list of numbers higher than 1000, you would select Equals and type >100
       
      Custom Filter
       
      Videos
  • Less Than...: You must type an exact value. For example, instead of using the Equals... option with the < operator, this option allows you to enter a value. Microsoft Access would consider the records whose values of that column are lower than the value you typed. For example, if you select Less Than and type 100, you would get the same result as if you have typed <100 with the Equals option. To get the records that have a value less than or equal to a certain value, you have two options:
    • You can right-click the desired value in the table, query or form and click the Less Than Or Equal To option
       
    • You can right-click any cell under the column or right-click the control or its label on a form, position the mouse on Number Filters, and click Equals. Then, in the Custom Filters dialog box, type <= followed by a number
  • Greater Than...: You must type an exact value. For example, if you type 100, Microsoft Access would create a list of records whose values of that column are higher than the value you typed.

    To get the records that have a value greater than or equal to a certain value, you have two options:

    • You can right-click the desired value in the table, query or form and click the Greater Than Or Equal To option
    • You can right-click any cell under the column or right-click the control or its label on a form, position the mouse on Number Filters, and click Equals. Then, in the Custom Filters dialog box, type >= followed by a number
  • Between...: If you click Between, a dialog box with two text boxes would come up:

    Between Numbers

    This option allows you to specify a range of values. In the top text box, you can type a small value. In the bottom text box, type a value equal or greater than the other. When you click OK, Microsoft Access would create a list of records in that range. Here is an example:


     

     
    You can also use the Equals option to get the same result. You would use the AND operator. Here is an example:
     

     

     
    We will study the AND operator in the next lesson but you should know that it is available.
 
Home Copyright © 2008-2016, FunctionX, Inc.