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

   

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

   

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

   

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

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

   

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

   

   

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

   

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

   

   

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

    

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

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

   

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

   

   

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

   

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

   

7. On the Ribbon, click the View button (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

   

   

10. On the status bar, click the Design View button
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

    

    

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

   

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

   

9. On the Ribbon, click the View button (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

    

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

    

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

   

10. On the Ribbon, click the Run button

    

11. After viewing the records, on the Ribbon, click the View button
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

    

13. On the Ribbon, click the Run button

    

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