Lessons Logo

Sorting/Filtering Numbers and Dates

 

Sorting and Filtering Numeric Fields

 

Sorting Numeric Fields

As seen in Lesson 16, 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.

 

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.

 

Data Analysis

Practical Learning: Introducing Sorting Records

  1. From the resources that accompany our lessons, open the Altair Realtors2 database
  2. In the Navigation Pane, double-click (the) Numbers and Dates (form) to open it
  3. Click any field under Market Value
  4. To see the list of properties from the cheapest to the most expensive, in the Sort & Filter section of the Ribbon, click the Ascending button
  5. On the Ribbon, click Clear All Sorts
  6. To view the list of properties from the oldest to the newest, right-click Year Built and click Sort Largest to Smallest
     
    Sorting Numeric Fields
  7. On the Ribbon, click Clear All Sorts
 

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.

 

 

Practical Learning: Filtering Records

  1. To see the list of only properties built in 2000, under the Year Built column, right-click 2000 and click Equals 2000
     
    Filter
     
    Filter
  2. Right-click anywhere under Year Built and click Clear Filter From Year Built
  3. To get a list of properties that have more than 1 bathroom, right-click any cell under Bathrooms, position the mouse on Number Filters and click Greater Than
  4. In the dialog box, type 1.50
     
  5. Click OK
     
    Filtering Bathrooms
  6. Click the Filtered button

Sorting Date and Time-Based Fields

 

Introduction

Dates and times represent valuable items of a database. Although they mostly use numbers, their values represent spatial occurrences and therefore are treated accordingly. As done for strings and numbers, fields with date and time values can be sorted. For date values, the results of sorting produce a chronological or a reverse chronological order. Because a date (or time) is a spatial value, it can be considered as occurring:

Sorting the Records

As we saw in Lesson 18, there are various formats and rules for date and time values. Consider the following table:

Hotel Management

When it comes to dates and times, you can sort values. When Microsoft Access is asked to sort dates or times, it refers to the Regional and Language Settings of Control Panel. This means that the rules may be different from one language to another.

To sort the records of a table, query, or form, first identify the column or control you will use. On the table or query, you can click a field under the column of your choice. On a form, you can click a control or its accompanying label. Then:

You can also right-click to sort date or time-based fields. Right-click the column header or a field under the column and click Sort Oldest to Newest

If the column or control contains empty values, they would appear first. After the empty records, the oldest record would show. The list would end with the record with the most recent value. As mentioned for the sorting of other types, each record is kept with its values for each column. Therefore, when the records are sorted, Microsoft Access first refers to the field you selected and displayed the other values of the corresponding records in their fields. If two records of a column have the same values, they are displayed in the same range and the records of the next column are sorted:

This scenario also applies to time-based columns.

As opposed to a chronological order, you can also sort records in reverse chronological order. To do this:

As mentioned already, after sorting a column, the corresponding values display on its left and right. If two records have the same value for a column, they are listed and Microsoft Access sorts the records of the next column in chronological order. If you want, you can sort the records of the next column in reverse chronological order. The first records sorted chronologically would keep their sequence and the records in the new column would be sorted:

This description applies to time-based column also.

Practical Learning:  Sorting Date-Based Fields

  1. Click any field under Date Listed
  2. To see the list of properties by the listed date, in the Sort & Filter section of the Ribbon, click the Ascending button
     
    Sorting
  3. In the Sort & Filter section of the Ribbon, click the Clear All Sorts button Clear All Sorts

Filtering Date and Time-Based Fields

 

Filtering by a Specific Date or Time

A date or a time can be considered either as a constant value or as a range of values. When examining the values of a list, you may want to include only some values and/or to exclude some others. This means that you can filter values based on a date, a time, or even a combination of both. To filter the records on a table, a query, or a form, once again you have various options. To get a list of records that occur at the same date or time, you can:

After clicking, the table or query would display only the records that share the (exact) same date or time in the field:

 

Filtering by (Only) a Specific Month, Day, or Year

Instead of a whole date or time, you can filter using only a specific day of all months, a specific month of all years, or a specific year in all records. These depend on how the values are entered and/or how the values display (or are accessed). For example, if a date-based column is configured to display its values in short date (Format = Short Date): 

Practical Learning: Filtering By Year

  1. Navigate to the second record and, in the Date Listed field, double-click 2008 to select it
     
    Properties
     
    Notice that the Record Number indicates 2 of 28
  2. On the Ribbon, click Selection and click Ends With 2008.
    Notice that the Record Number displays 1 of 15
  3. Under the form, click the Filtered button

Filtering Records Related to the Current Date

If you are working on a series of records that use the same values as your current week, your current month, your current quarter, or your current year, you can use one of these values as a base to filter. To do this, you can right-click a date in a field or an empty field for a date, position the mouse on Date Filters and select from the list:

Once again, remember that when you select an option, Microsoft Access refers to the system clock of the computer on which the operation is being performed:

If you select Microsoft Access
This Week Finds all records entered for the current week, not the last few days and the next few days. Microsoft Access uses the rules of the language of the computer. For example, in US English, the week starts on Sunday and ends on the following Saturday. If you are on Friday and select This Week, Microsoft Access would consider records from Sunday of this week until tomorrow Saturday
Last Week Finds the records that were entered for last week, not the last 7 days. Microsoft Access uses This Week as the basis
Next Week Finds all records that have been entered for next week, not for the next 7 days
This Month Finds all records that have been entered for this month
Last Month Finds all records that exist for last month, not the last 30 days
Next Month Finds all records that exist for next month, not for the next 30 days
This Quarter Finds all records that have been entered for this quarter
Last Quarter Finds all records that have been entered for last quarter
Next Quarter Finds all records entered for the next quarter
This Year Finds all records entered for this year
Last Year Finds all records entered for last year
Last Year Finds all records entered for last year
Year To Date Finds all records that occur from the beginning of this year to now. Remember that to determine now, Microsoft Access refers to the system clock

Filtering a Range of Dates or Times

If you do not know the exact date you want to filter by, you can/should consider working on a range of dates. You have many options. To start, you can right-click any field under the column of a table or query, or right-click a control or its accompanying label on a form, position the mouse on Date Filters and choose one of the options. If you click Equals..., Does Not Equal..., Before..., or After... option, the Custom Filter dialog box would come up. To assist you with filtering and to select an exact date, the Custom Filter dialog box is equipped with a picture button that, when clicked, would display a calendar:

Custom Filter

When using the Custom Filter dialog box for a date-based column, you can type an exact date or use the calendar to select a date, which would reduce the likelihood of mistakes. If you are working on a time-based field, then you must type a valid time value in standard or military format.

Consider the following table of customers staying in a hotel:

Consider the following table of customers hotel staying records

If you right-click a cell under Reservation or right-click a control for the reservation date, position the mouse on Date Filters and click:

If you right-click a cell under a column or right-click a control or its accompanying label for a date, position the mouse on Date Filters and click Between... Alternatively, you can click a date-based field. Then, on the Ribbon, click Selection and click Between. In both cases, the Between Dates dialog box with two text boxes would display. In each text box, you can type a specific date or add a comparison operator to a date or time. This means that you can enter an exact date for each text box. Here is an example:

Between Dates

When you click OK, Microsoft Access would look for records that occur between both dates:

Notice that, on the Between Dates dialog box, the first label uses the caption Oldest and the other text box uses the Newest label. This is used only as a guide. You can enter a newer date in the first text box and an older date in the second text box:

You would get the same result. Although there is hardly any justification for that, you can use comparison operators in the text boxes, in none of the text boxes, or in one and not the other text boxes. Microsoft Access would apply the operators and use Boolean algebra to get the results.

Using the Between Dates dialog box is equivalent to using the logical AND operator that we will study in Lesson 25.

Practical Learning: Filtering a Range of Dates or Times

  1. Right-click Date Listed, position the mouse on Date Filters and click After...
  2. Click the calendar button and select the date corresponding to March 22, 2008
  3. Click OK
     
    Properties
  4. Right-click the Date Listed: label and click Clear Filter From Date Listed
  5. Click Date Listed: label
  6. On the Ribbon, click Selection, and click Between...
  7. In the Top text box, type 06/06/07
  8. In the other text box, type 06/06/08
  9. Click OK to see the result
  10. On the Ribbon, click Toggle Filter (or Remove Filter)
  11. Close the form. If asked whether you want to save, click No

Sorting and Filtering Boolean Fields

 

Introduction

As seen in Lesson 13, a Boolean field is one that holds a true or false result and it is created as a Yes/No type. By default, and most of the time, a Boolean field displays a check box that can be checked (or marked) or cleared (or unchecked). By its Boolean interpretation, Microsoft Access considers that a check box that is checked is "selected" and a check box that is not checked in "cleared". You can use this logic to sort the records of a table, a query, or a form.

You can sort the Boolean records as selected or cleared. As it should seem obvious, when you sort in selected order, the checked boxes would come up first. To sort the Yes/No records of a table, a query, or a form, in selected order, you can:

To sort the Boolean records in cleared order:

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 Clear All Sorts.

Practical Learning: Introducing Sorting Records

  1. In the Navigation Pane, double-click Properties: Table
  2. To re-arrange the list of properties starting with those with a finished basement, right-click Finished Basement and click Sort Selected to Cleared
     
    Sorting 
  3. On the Ribbon, click Clear All Sorts
  4. Press the left arrow key to remove focus from the Finished Basement column

Filtering Boolean Records

If you have a column created as a Yes/No type, you can isolate the list with only checked or only cleared fields. To filter records that are checked:

After clicking, the table, query, or form would display only the records that have that field checked.

To filter records by the cleared check boxes:

After clicking, the table, query, or form would display only the records that have that field cleared.

Practical Learning: Filtering Records

  1. To see the list of properties that do not have a garage, right-click any check box under Indoor Garage and click Is Not Selected
     
  2. Close the table
  3. When asked whether you want to save the table, click No
  4. In the Navigation Pane, under Properties: Table, double-click Properties
  5. To review the list of properties that have a finished basement, right-click Is the Basement Finished? and click Is Selected
     
    Properties
  6. Close the form

MCAS: Using Microsoft Office Access 2007 Topics

 
P1 Sort data
 

Exercises

 

Yugo National Bank

  1. Open the Yugo National Bank1 database
  2. Open the Customers table and sort the records by the date created
  3. Do not save the table

Watts A Loan

  1. Open the Watts A Loan1 database
  2. Open the Customers form and show only the accounts that were in 2008 on April 15

World Statistics

  1. Open the World Statistics1 database
  2. Open the Countries form and show only the countries that got their independence in 1960
  3. Show the countries that have their national holiday in July
  4. Do not save anything

US Senate

  1. Open the US Senate1 database 
  2. Open the Senators form and make it form show only the senators who were elected between 2002 (included) and 2006 (included). Do not save the form

Previous Copyright © 2002-2008 Yevol Next