Home

Introduction to Sorting Records

Fundamentals of Sorting Records

Introduction

Sorting records consists of rearranging them in alphabetical, incremental, chronological, or Boolean order. Sorting can be performed on fields of any type of value. Fields also may not display any value but can be sorted too.

Practical LearningPractical Learning: Introducing Record Sorting

  1. Start Microsoft Access
  2. From the resources that accpmany these lessons, open the Chemistry2 database
  3. In the Navigation Pane, double-click the Elements table

    Filter

Sorting Records in the Regular View

There are two categorical ways to produce sorted records. To sort records while you are viewing them, display the table or the query in Datasheet View, the form in Form View, or the report in Layout View. Then use the options in the Sort & Filter section of the Ribbon.

To sort records, you must first decide what field will be used as the reference. To arrange the list in alphabetical, incremental, chronological, or Boolean order:

  • On the table or query, a form displaying in the Datasheet View:
    • Click the down-pointing button on the right side of the column header:

      Filter

      and click Sort A to Z
    • Click a cell in the column of your choice. In the Sort & Filter section of the Ribbon, click Ascending Descending
    • Right-click a cell in the column of your choice and click Sort A to Z
  • On a form in Form View or in a tabular format, or a report in Layout View:
    • Click the text box or the control (such as a combo box) of your choice. In the Sort & Filter section of the Ribbon, click Ascending Descending
    • Right-click the text box or the control (such as a combo box) of your choice and click Sort A to Z

Practical LearningPractical Learning: Sorting Records

  1. Click the down-pointing button on the right side of Element and click Sort A to Z

    Sorting Records

    Sorting Records

  2. Close the table
  3. When asked whether you want to save, click No
  4. On the Ribbon, click File and click Open
  5. From the resources that accompany these lessons, open the StatesStatistics3 database
  6. In the Navigation Pane, double-click the Summary form to open the tabular form:

    States Statistics - Viewing Records

  7. On the Ribbon, click Home
  8. On the form, click anything below State
  9. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

    Form - Sorting Records

  10. On the form, right-click anything below Region and click Sort A to Z

    Form - Sorting Records

  11. Close the form
  12. On the Ribbon, click File and click Open
  13. In the list of files, click Chemistry2
  14. In the Navigation Pane, right-click the Elements report and click Layout View
  15. On the report, right-click anything below Name and click Sort A to Z

    Report - Sorting Records

    Report - Sorting Records

  16. On the Ribbon, click Home
  17. On the report, click anything below Symbol
  18. In the Sort & Filter section of the Ribbon, click the Ascending button Descending

    Report - Sorting Records

  19. Close the report
  20. When asked whether you want to save, click No
  21. On the Ribbon, click File and click Open
  22. In the list of files, click StatesStatistics3
  23. In the Navigation Pane, double-click the States table to open it in Datasheet View

    Table - Sorting Records

  24. On the table, click the down-pointing button on the right side of State and click Sort A to Z

    Table - Sorting Records

 
 
 

Unsorting the Records

When you have finished sorting the records, you may want to reset the list before continuing with the next operation. To assis you with this, the Sort & Filter section of the Ribbon is equipped with the Remove Sort button Clear All Sorts.

To put the list back in the sequence it previously had:

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

Practical LearningPractical Learning: Dismissing the Sorting Operation

  1. In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts

    Table - Sorting Records

  2. Right-click any cell below Region and click Sort A to Z
  3. In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts
  4. Click any cell below Capital
  5. In the Sort & Filter section of the Ribbon, click the Ascending button Ascending
  6. In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts
  7. Close the table
  8. When asked whether you want to save, click No

Sorting Records in Descending Order

Besides the regular arrangement of records, you can also sort records in reverse alphabetical, incremental, or chronological. To do this:

  • On the table, query, or form in Datasheet View:
    • Click the down-pointing button on the right side of the column header and click Sort Z to A
    • Click a cell in the column of your choice. In the Sort & Filter section of the Ribbon, click Descending Descending
    • Right-click a cell in the column of your choice and click Sort Z to A
  • On the form in a view other that Design View or a report in Layout View:
    • Click the text box or the control (such as a combo box) of your choice. In the Sort & Filter section of the Ribbon, click Descending Descending
    • Right-click the text box or the control (such as a combo box) of your choice and click Sort Z to A

Record Sorting

Practical LearningPractical Learning: Sorting Records in Descending Order

  1. In the Navigation Pane, double-click the States table
  2. Click the down-pointing button on the right side of State and click Sort A to Z

    Record Sorting

    Record Sorting

  3. Click the down-pointing button on the right side of State and click Sort Z to A

    Record Sorting

  4. In the Sort & Filter section of the Ribbon, click the Remove Sort button Clear All Sorts
  5. Click a cell below Region
  6. In the Sort & Filter section of the Ribbon, click the Ascending button Ascending

    Record Sorting

  7. In the Sort & Filter section of the Ribbon, click the Descending button Descending

    Record Sorting

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

Sorting Records by Design

If you include the sorting operation in the design of a table, you must save the table before viewing the result. If you save the table like that, the sorting feature would be included as belonging to the design of the table.

To prepare a table for record sorting in design:

  • Display the table in Design View. In the Property Sheet of the table, click the Order By field. Type the name of the field that will be used as reference. You must first save the table before displaying it in the Datasheet View to see the result
  • Display the query in Design View and select the field that will be used as reference. In the bottom side of the window, click the combo box that corresponds to the Sort row of the field and select Ascending
  • Display the form or the report in Design View:
    • If the Record Source is set to a table or query, access the Property Sheet (that of the form or report). In the Data or the All tab of the Property Sheet of the form, click the Order By field. Type the name of the field (or control) that will be used as reference
    • If the Record Source is a SQL statement, click the ellipsis button Browse of the Record Source. In the bottom side of the window, click the combo box that corresponds to the Sort row of the field and select Ascending

Practical LearningPractical Learning: Creating a Sorted Query

  1. On the Ribbon, click File and click Open
  2. Open the Ceil Inn3 database
  3. In the Navigation Pane, double-click the Employees table to view its records

    Ceil Inn - Viewing the Records of a Table

  4. After viewing the records, right-click the table title bar and click Design View
  5. If the Property Sheet is not available, right-click anything below Field Name and click Properties.
    In the Property Sheet of the table, click Order By and type LastName

    Ceil Inn - Sorting the Records of a Table

  6. In the Views section of the Ribbon, click the View button Datasheet View to display the table in Datasheet View
  7. When asked whether you want to save, click Yes

    Ceil Inn - Sorting the Records of a Table

  8. Close the table
  9. On the Ribbon, click File and click Open
  10. In the list of files, click Altair Realtors2
  11. To start a query, on the Ribbon, click Create and click Query Design
  12. In the Show Table dialog box, double-click Properties and click Close
  13. In the top list, double-click PropertyNumber, City, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, and MarkedValue
  14. In the bottom side of the window, click PropertyType and press the down arrow key twice
  15. Click the arrow of the Sort combo box and select Ascending

    Query = Sorting Records

  16. Close the query
  17. When asked whether you want to save, click Yes
  18. Set the name as Listing by Type and press Enter

Primary Topics on Sorting Records

Sorting Records in SQL

In SQL, to sort a field in ascending order, you primarily don't have to do anything because the sorting feature is included in the default SELECT statement. Still, if you want to indicate that you want to sort records, include the ORDER BY expression at the end of the SELECT statement. This expression must be followed by the name of the column used as reference. The formula to follow is:

SELECT what FROM what-object ORDER BY what-field;

The field used as the basis should be recognized as part of the selected columns. Here is an example:

SELECT ItemName, UnitPrice FROM StoreItems ORDER BY ItemName;

Remember that you spread a SQL statement to many lines. In this case, you can put the ORDER BY clause on its own line. Here is an example:

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. Here is an example:

SELECT * FROM Employees ORDER BY LastName;

As mentioned already, by default, records are ordered in ascending order. Nevertheless, the ascending order is controlled by the ASC keyword specified after the based field. Here is example:

SELECT FirstName, LastName 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. Here is an example:

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. 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 and click Query Design
  4. On the Show Table dialog box, click Close
  5. On the status bar, click the SQL View button
  6. Change the code as follows:
    SELECT ItemNumber, SubCategory, ItemName, UnitPrice
    FROM StoreItems ORDER BY ItemName;
  7. To see the results, on the status bar, click the Datasheet View button Datasheet View
  8. Right-click the title bar of the Query window and click SQL View
  9. To explicitly sort recored in ascending order, change the code as follows:
    SELECT ItemNumber,
           SubCategory,
           ItemName,
           UnitPrice
    FROM StoreItems
    ORDER BY ItemName ASC;
  10. To see the results, on the status bar, click the Datasheet View button Datasheet View
  11. On the status bar, click the SQL View button
  12. To sort records in descending order, change the code as follows:
    SELECT ItemNumber,
           SubCategory,
           ItemName,
           UnitPrice
    FROM StoreItems
    ORDER BY ItemName DESC;
  13. In the Views section of the Ribbon, click the View button
  14. Close Microsoft Access
  15. When asked whether you want to save, click No
 
 
   
 

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