Home

Topics on Sorting Records

Sorting Records Based on Type

Record Sorting and Null Fields

When some values are not available, a table, a query, a form, or a report may display empty fields. These are also referred to as null fields or null values. In Lesson 22, we saw what a null or an empty field is.

When sorting records in alphabetical, incremental, or chronological order, the empty fields always come first. When sorting records in reverse alphabetical, decrementing, or reverse chronological order, the empty fields will always come last in the result. On the other hand, if you sort the records in descending order, the non-null records would come first.

Practical Learning: Sorting Records

  1. Start Microsoft Access
  2. In the list of files, click Altair Realtors2 from the previous lesson
  3. In the Navigation Pane, double-click the Properties table
  4. In the Home tab of the Ribbon, change the following characteristics:
    Font Name: Calisto MT (if you don't have that font, select Times New Roman)
    Font Color: Green 1 (Standard Colors: 7th column, 2nd row)
    Background Color: More Colors: Red: 210, Green: 75, Blue: 25
    Alternate Row Color: More Colors: Red: 235, Green, 135, Blue: 100

    Introducing Logical Disjunction

  5. On the table, click the down-pointing button on the righ side of City and click Sort A to Z

    Query = Sorted Records

  6. Click a cell below City
  7. In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts
  8. Click the down-pointing button on the right-side of Condition and click Sort A to Z

    Table = Sorting Records

  9. Close the table
  10. When asked whether you want to save, click No

Sorting Number-Based Fields

As you may know already, the SQL supports various types of numeric values. The columns that use those values can be sorted in incremental order.

To sort records based on a number-based column:

  • Display the table, the query, or the form in the Datasheet View. Click the down-pointing button on the right side of the column header and click Sort Smallest to Largest
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Right-click a number-based field or text box and click Sort Smallest to Largest
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Click a number-based field or text box. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

In the same way, you can sort the records in the reverse order. Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Practical LearningPractical Learning: Sorting Number-Based Fields

  1. Open the StatesStatistics3 database from the previous lesson
  2. In the Navigation Pane, double-click the States table to open it in Datasheet View
  3. In the Home tab of the Ribbon, change the following characteristics:
    Change the following characteristics:
    Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
    Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
    Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
    Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)

    Table - Sorting Number-Based Fields

  4. On the table, click the down-pointing button on the right side of Sqr Miles and click Sort Smallest to Largest

    Sorting Number-Based Fields

    Table - Sorting Records by a Number-Based Field

  5. Close the table
  6. When asked whether you want to save, click No
  7. In the Navigation Pane, right-click the Summary report and click Layout View

    Report - Sorting Records on a Number-based field

  8. On the report, right-click any text box in the Order column and click Sort Smallest to Largest

    Report - Sorting Records on a Number-based field

    Report - Sorting Records on a Number-based field

  9. On the Ribbon, click Home
  10. On the report, click any text box below Sqr Miles
  11. In the Sort & Filter section of the Ribbon, click the Descending button Descending

    Report - Sorting Records on a Number-based field

  12. Close the report
  13. When asked whether you want to save, click No

Sorting Boolean Fields

Boolean fields are those that use False and True values. To sort records based on a Boolean field:

  • Display the table, the query, or the form in the Datasheet View. Click the down-pointing button on the right side of the column header and click Sort Selected to Cleared
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Right-click a check-based field and click Sort Selected to Cleared
  • Display a table, a query, or a form in the Datasheet View, or a report in the Layout View. Click a check-based field. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

In the same way, you can sort the records in the reverse order. Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Practical LearningPractical Learning: Sorting Boolean Fields

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Altair Realtors2 used earlier
  3. In the Navigation Pane, double-click the Listing by Type query

    Query = Sorting Records Based on a Boolean Field

  4. On the query, click the down-pointing button on the right-click of If Basement Finished? and click Sort Selected to Cleared:

    Query = Sorting Records Based on a Boolean Field

    Query = Sorting Records Based on a Boolean Field

  5. Press Tab and make sure focus is on the Has Indoor Garage column
  6. On the Ribbon, click Home and click Ascending:

    Query = Sorting Records Based on a Boolean Field

  7. Close the query
  8. When asked whether you want to save, click No
 
 
 

Sorting More Than One Column

Introduction

Imagine you arrange records based on a certain column that has repeating values:

ELECT Region, 
      StateName,
      Capital,
      AreaSqrMiles, 
      AdmissionUnionOrder
FROM States
ORDER BY Region;

If you get a situation where many records on a column have the same value, you can specify an additional column by which to sort the records. To arrange the list using more than one column using the SQL, after ORDER BY, type the columns separated by commas. Here is an example:

SELECT Region, 
       StateName,
       Capital,
       AreaSqrMiles, 
       AdmissionUnionOrder
FROM States
ORDER BY Region, StateName;

Practical LearningPractical Learning: Sorting More Than One Column

  1. On the Ribbon, click File and click Open
  2. In the list of files, click StatesStatistics3 accessed earlier
  3. To start a new query, on the Ribbon, click Create and click Query Design
  4. On the Show Table dialog box, double-click States and click Close
  5. In the list, double-click Region, StateName, Capital, AreaSqrMiles, and AdmissionUnionOrder
  6. To see the results, in the Results section of the Ribbon, click the Run button Run

    Sorting Records

  7. On the Ribbon, click the View button Design View (or click the arrow button below View and click Design View)
  8. To arrange the list of states by regions, in the bottom side of the window, double-click the first empty box below Region for the Sort box to set it to Ascending
  9. To see the results, in the Results section of the Ribbon, click the Run button Run

    Sorting Records

    Query = Sorting Records on Many Fields

  10. On the status bar, click the Design View button Design View
  11. To arrange the list of states by regions, in the bottom side of the window, double-click the first empty box below StateName for the Sort box to set it to Ascending
  12. To see the results, in the Results section of the Ribbon, click the Run button Run

    Sorting Records

    Query = Sorting Records on Many Fields

  13. Close the query
  14. When asked whether you want to save, click No

Sorting Null and Non-Null Fields

If you specify more than one record to sort by, the database engine sorts the primary column first. Then, on the second field, when two records have the same value, the NULL values would come first.

Sorting Many Number-Based Fields

If you have two integer-based fields that have repeating values, if you sort the list based on the first field, the records with similar values in the first field would be grouped. If the records are sorted in the second field, they would be sorted for each group of similar first field records.

Options on Sorting Records

Sorting by an Expression

When sorting the records, the database engine mostly needs to have a value as reference, the value by which to arrange the records. Based on this, besides, or inside of, (a) column(s), you can use an expression to sort the records.

Practical LearningPractical Learning: Sorting by an Expression

  1. To start a new query, on the Ribbon, click Create and click Query Design
  2. On the Show Table dialog box, double-click States and click Close
  3. In the list, double-click AdmissionUnionOrder
  4. In the bottom side of the window, click and press Tab
  5. In the empty Field box, type State: [StateName] & '(' & [Abbreviation] & ')'
  6. Press Tab and type Area: [AreaSqrMiles] & ' Square Miles (' & ([AreaSqrMiles]*2.59) & ' Square Kilometers)'
  7. In the top list, double-click AdmissionUnionDate and AreaSqrMiles

    Sorting Records

  8. To see the results, in the Results section of the Ribbon, click the Run button Run

    Query = Sorting Records Using an Expression

  9. On the Ribbon, click the View button Design View (or click the arrow button below View and click Design View)
  10. To arrange the list by the state, in the bottom side of the window, double-click the Sort box that corresponds to the State column

    Sorting Records

  11. To see the results, in the Results section of the Ribbon, click the Run button Run

    Query = Sorting Records Using an Expression

  12. Close the query
  13. When asked whether you want to save, click No

Using a Function to Sort Records

Just as you use an expression as a basis for sorting records, you can use the return value of a function to arrange records.

Practical LearningPractical Learning: Sorting Using a Function

  1. On the Ribbon, click File and click Open
  2. In the list of files, click FunDS3 from the previous lesson
  3. On the Ribbon, click Create
  4. To create a query, in the Queries section, click the Query Design button
  5. In the Show Table dialog box, double-click StoreItems
  6. Click Close
  7. From the list, double-click ItemNumber, DateInStore, ItemName, and UnitPrice
  8.  In the bottom side of the window, click the empty box on the right side of UnitPrice and type Today: Date()
  9. Click the empty box on the right side of Today and type Days in Store: DateDiff('d',[DateInStore],Date())

    Query Design - Sorting Records Using Functions

  10. On the Ribbon, click the Run button Run

    Query Design - Sorting Records Using Functions

  11. After viewing the records, on the Ribbon, click the View button Design View
  12. In the bottom side of the window, click the Sort button that corresponds to  the last column, then click the arrow of the combo box and select Sort

    Query Design - Sorting Records Using Functions

  13. On the Ribbon, click the Run button Run

    Query Design - Sorting Records Using Functions

  14. When asked whether you want to save, click Yes
  15. Set the name as Inventory Evaluation and click OK
  16. Close Microsoft Access
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next