There may be times when you want to sort data, not by alphabetical or numerical order, but by user-defined order (also called custom or logical order). For example, sorting weekdays so that Monday appears first makes more sense than sorting in alphabetical order, where Friday appears first. You can sort data in the Datasheet view of a table or query, or in the Form view of a form.
What do you want to do?
Sort a small, finite number of unique field values
In this procedure, you will write an expression that uses the IIf function to rank the titles of employees.
First, create a table that contains three fields — Title, FirstName, and LastName. In the Title field, enter the following values for four new records:
-
Inside Sales Coordinator
-
Sales Manager
-
Sales Representative
-
Vice President, Sales
In the FirstName and LastName fields of the four records, enter any names you want.
-
Now, open the table in Datasheet view.
-
On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.
-
Add the fields you want, such as FirstName, LastName, and Title, to the grid.
-
Right-click the Field row in the first blank column, click Zoom, and then type the following expression:
IIf ([Title] = "Vice President, Sales", 1, IIf ([Title] = "Sales Manager", 2, IIf ([Title] = "Sales Representative", 3, IIf ([Title] = "Inside Sales Coordinator", 4, 5))))
Title is the name of the field that contains the values that are being ranked. The values enclosed in quotation marks are the individual values stored in the field. The numeric value that follows is the rank assigned to it. The preceding expression assigns ranks 1 through 4 to four different titles, and assigns rank 5 to all others. When you perform the sort, the titles not mentioned in this expression are assigned the same rank (5).
-
In the Sort row for the column containing the expression, click Ascending.
-
On the Home tab, in the Sort & Filter group, click Toggle Filter.
The IIf function returns the numeric value that corresponds to the value in the Title field. For example, if the value in the Title field is Sales Manager, the number assigned is 2. If there is a value in the Title field not listed as an argument in the function — for example, District Coordinator — it is assigned the number 5. The query then sorts these numbers in ascending order.
Sort a large number of unique field values
If you have a large number of values to rank, a better way to sort data is by creating a lookup table.
Assume that the Employees table refers to more than 50 different titles. You will first need to create a lookup table and prepare the existing table to look up the new table. You then relate the two tables and create a query.
Create a lookup table and modify the existing table
-
Create a new lookup table that will store the titles. Name the table tblTitles, and set the fields to the data types indicated in parentheses, as shown in the following table:
TitleID (AutoNumber)
Title (Text)
1
Vice President, Sales
2
Sales Manager
3
Sales Representative
4
Inside Sales Coordinator
-
Set the TitleID field as the primary key.
-
Modify the Employee table so that it looks up the tblTitles table.
-
In Design view, change the name of the Title field to TitleID.
-
In the Data Type column, change the Data Type to Number.
-
-
On the General tab, under Field Properties, change Field Size to Long Integer.
-
Save the table and ignore the warning about the loss of data.
-
Switch to Datasheet view.
-
Populate the Titles column with the TitleID value that corresponds to the title value.
For example, for records where the Title value is Sales Manager, type 2. For Sales Representative, type 3.
-
Save and close the table.
Relate the two tables
-
On the Database Tools tab, in the Relationships group, click Relationships.
-
On the Design tab, in the Relationships group, click Add Tables (or Show Tables in Access).
-
Select tblTitles and Employees, and click Add to add them to the Relationships window. Close the Show Table dialog box.
-
Drag the TitleID field from the tblTitles table to the TitleID field in the Employees table.
The Edit Relationships dialog box opens.
-
Ensure that the field names displayed in the two columns are both TitleID, and also that the Relationship Type box displays One-To-Many. You can change the field names, if necessary.
-
Select the Enforce Referential Integrity check box.
-
Click Create to create the relationship.
The Edit Relationships dialog box closes, and saves the changes.
-
Close the Relationships tab.
Create a query
-
On the Create tab, in the Queries group, click Query Design.
-
In the Add Tables (Show Table in Access) dialog box, click the Tables tab, double-click Employees, and then double-click tblTitles.
-
Add the EmployeeID, LastName, and FirstName fields from the Employees table and the Title field from the tblTitles table to the query design grid.
-
Switch to Datasheet view.
