Access 97/2000: Sorting Records in a User-Defined Order

In Microsoft Access, there are times when you want to sort data not by alphabetical or numerical order, but by user-defined order (also called logical order). For example, the Employees table in the Northwind sample database has a Title field that you might want to sort by job responsibility:

1. Vice President, Sales
2. Sales Manager
3. Sales Representative
4. Inside Sales Coordinator

Notice that neither an ascending nor descending alphabetical sort order would work here. You can put the records in the order you want by assigning numeric values to each title. To do so, create a query with a calculated expression that uses either the Switch or the IIF function. Which function you choose depends on whether you want Title field values not listed in the expression to be assigned a Null value or a numeric value. Both functions work best when you have a small number of possible values and you don't need to change the values often.

To sort records in a user-defined order

1. Open the Northwind sample database.
   The default location of the Northwind sample database is the Program Files\Microsoft Office\Office\Samples folder. If you don't see Northwind.mdb in your Samples folder, or if you need more information about opening Northwind, type open the northwind sample database in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
2. On the Insert menu, click Query.
3. In the New Query dialog box, click Simple Query Wizard, and then click OK.
4. In the Tables/Queries box, click Table: Employees.
5. Add LastName, FirstName, and Title to the Selected Fields list, and then click Next.
6. Type Employees Sorted by Job Responsibility as the title for your query.
7. Click Modify the query design, and then click Finish.
8. Right-click the Field cell in the first blank column, click Zoom, and then enter one of the following calculated expressions:
   If you want Title field values not listed in the expression to be assigned a Null value, use the Switch function:
   Switch([Title] = "Vice President, Sales", 1, [Title] = "Sales Manager", 2, [Title] = "Sales Representative", 3, [Title] = "Inside Sales Coordinator", 4)
   If you want Title field values not listed in the expression to be assigned a value other than Null (such as 5), use a nested IIF function:
   IIF([Title] = "Vice President, Sales", 1, IIF([Title] = "Sales Manager", 2, IIF([Title] = "Sales Representative", 3, IIF([Title] = "Inside Sales Coordinator", 4, 5))))
9. In the Sort cell for the column with the expression, click Ascending.
10. In the same column, clear the Show check box so you don't see the calculated column.
    If you want to see the values returned by the function, leave the Show check box selected.
11. Switch to Datasheet view to see your results.
    The function returns the numeric value that corresponds to each Title field value you specify in the expression. For example, if the value in the Title field is Sales Manager, then the number assigned is 2. If there's a value in the Title field not listed as an argument in the function - for example, District Coordinator - then the Switch function assigns a Null value and the IIF function assigns the number 5. The query then sorts these numbers in ascending order.