Introduction to Sorting Records

Fundamentals of Sorting Records

Introduction

Practical 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

   

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:

    

    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
  • 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
  • Right-click the text box or the control (such as a combo box) of your choice and click Sort A to Z

Practical Learning: Sorting Records

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

   

   

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:

   

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

   

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

    

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

    

    

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

    

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

    

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

    

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 .

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

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

Practical Learning: Dismissing the Sorting Operation

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

   

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
4. Click any cell below Capital
5. In the Sort & Filter section of the Ribbon, click the Ascending button
6. In the Sort & Filter section of the Ribbon, click the Remove Sort button
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
  • 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
  • Right-click the text box or the control (such as a combo box) of your choice and click Sort Z to A

Practical 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

   

   

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

   

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

   

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

   

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

   

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

   

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

   

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

    

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