Show All

Hide All

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, the Employees table in the Northwind Traders sample database has a Title field that you might want to sort by job responsibility:

• Vice President, Sales
• Sales Manager
• Sales Representative
• Inside Sales Coordinator

Note that neither an ascending nor descending alphabetical sort order would work here. The solution is to assign a rank to each of the values, and then sort the records based on the ranking.

This topic covers three different procedures for sorting records in custom order.

Sort records based on a small, finite number of values in a table, query, or form

Sort records based on a large number of values in a query or form

Allow users to sort records in custom order in Page view

Sort records based on a small, finite number of values in a table, query, or form

In this procedure, you will create a query with a calculated expression that uses either the Switch function or the IIf function to rank the titles of employees. 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. Note that you can use the same technique in the Advanced Filter/Sort dialog box when working with tables and forms.

1. Open the Northwind Traders sample database.

   The default location of the Northwind Traders 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, 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 — the Switch function assigns a null value and the IIf function assigns the number 5. The query then sorts these numbers in ascending order.

Sort records based on a large number of values in a query or form

If you have a larger number of values, a better way to sort data is by using the AutoLookup feature in Access. You can use AutoLookup to create a query or form that automatically looks up information in the one-side table of a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) based on an entry in a many-side field. You can use this same technique to sort records in a user-defined order.

First, create the tables

Next, create the relationship

Finally, create the query

Allow users to arrange records in a custom order in Page view

You can allow users to define a custom sort order for records on a data access page (data access page: A Web page designed for viewing and working with data from the Internet or an intranet. Its data is typically stored in an Access database.), when ascending or descending order is not meaningful. For example, on a page that lists employee names and titles, the user might want to sort on the basis of title seniority. You can enable the user to rearrange records in any order while viewing the data access page in Page view or in Microsoft Internet Explorer.

Note  To complete the steps that follow, you must be familiar with creating and modifying data access pages, and writing code using the VBScript programming language.

The following illustration shows an Employees data access page. When you open the page, the records are sorted in ascending order, based on the ID field.

When you move the cursor to a record, up and down arrows appear next to the ID field of the record. Click the up or down arrow to move the current record either above the previous record or below the next record.

The following illustration shows the Employees page after the records have been arranged in custom order.

The page is based on an Employees table that has the following fields: ID, Title, Name, and RecordPosition. The RecordPosition field is used to track the current position of each record. The data access page includes the RecordPosition field, but in the previous illustration, its corresponding text box is hidden. The value in the field is updated each time the user moves a record to reflect the new position of the record. The following illustration shows the initial page with the RecordPosition field visible.

After the user moves the fourth record by clicking the up arrow, the record moves to the third position, and the record that was in the third position moves to the fourth position. The script attached to the up arrow updates the RecordPosition field of the third and fourth records to reflect their new positions in the recordset.

The following illustration shows the updated RecordPosition field values.

To create a data access page that supports custom sorting   

1. If the underlying record source does not include a field to store record-position information, add a numeric field named RecordPosition.
2. In Design view, create a data access page with the fields and records you want, including the RecordPosition field. Place the fields in tabular layout.

   Tip   When you drag a table, query, or multiple fields from a table or query from the field list to the page, if the Control Wizards tool in the toolbox is selected, Access displays the Layout Wizard. This wizard lets you choose the layout of the page.

3. In Design view, set the Visibility property of the RecordPosition text box to Hidden. Also set the DefaultSort group level property to RecordPosition ASC.
4. Add two image controls to the Header section to display up and down arrows. Set the Id property of the image controls to ImgUp and ImgDown.
5. Set the Src property of the image controls to the path and file name of the image files that contain the up and down arrow images.
6. Add code to the onmouseover event of the document object to track the current section and record, and show the corresponding up and down arrows.

   Sample onmouseover event procedure of the document object

   <SCRIPT language=vbscript event=onmouseover for=document><!--
   Dim elemT
   Dim sectT
   
   ' What element is the mouse pointer over? If it is the same as the last time, do nothing.
   Set elemT = window.event.srcElement
   If (Not (elemOver Is Nothing)) Then
   If (elemT.sourceIndex = elemOver.sourceIndex) Then
   Exit Sub
   End If
   End If
   
   ' If the mouse pointer is over a new element, do the following.
   Set elemOver = elemT
   
   ' What section is the mouse pointer over?
   Set sectT = MSODSC.GetContainingSection(elemT)
   
   ' If the mouse pointer is not over a section, do nothing unless the pointer was
   ' over a section previously.
   If (sectT Is Nothing) Then
   If (Not (elemSectOver Is Nothing)) Then
   If (Not (elemImgUp Is Nothing)) Then
   elemImgUp.style.visibility = "hidden"
   elemImgDown.style.visibility = "hidden"
   Set elemImgUp = Nothing
   Set elemImgDown = Nothing
   End If
   Set elemSectOver = Nothing
   End If
   Exit Sub
   End If
   
   ' If it is the same section that the mouse pointer was last over, do nothing.
   Set elemT = sectT.HTMLContainer
   If (Not (elemSectOver Is Nothing)) Then
   If (elemT.sourceIndex = elemSectOver.sourceIndex) Then
   Exit Sub
   End If
   End If
   
   ' If the mouse pointer is over a new section, record this.
   Set elemSectOver = elemT
   
   ' If up/down arrows are visible, hide them now.
   If (Not (elemImgUp Is Nothing)) Then
   elemImgUp.style.visibility = "hidden"
   elemImgDown.style.visibility = "hidden"
   End If
   
   ' Show the new up/down arrow images.
   Set elemImgUp = elemSectOver.children("imgUp")
   Set elemImgDown = elemSectOver.children("imgDown")
   If (Not (elemImgUp Is Nothing)) Then
   elemImgUp.style.visibility = "inherit"
   elemImgDown.style.visibility = "inherit"
   End If
   -->
   </SCRIPT>
   
7. Add code to the onclick event of the ImgUp control to move the current record above the previous record and update the RecordPosition field.

   Sample onclick event procedure of the ImgUp control

   <SCRIPT language=vbscript event=onclick for=imgUp>
   <!--
   Dim sect
   Dim sect2
   Dim rs
   Dim iOrder
   
   Set sect = MSODSC.CurrentSection
   Set sect2 = sect.PreviousSibling

   ' If record is not already at the top, do this.
   If (sect2.type = msodsc.constants.sectTypeHeader) Then
   iOrder = CLng(sect.HTMLContainer.children("RecordPosition").innerText)
   Set rs = sect.DataPage.Recordset

   ' Update the RecordPosition fields.
   rs.Fields("RecordPosition").Value = iOrder - 1
   rs.UpdateBatch
   sect2.MakeCurrent
   rs.Fields("RecordPosition").Value = iOrder
   rs.UpdateBatch

   rs.Sort = sect.DataPage.Recordset.Sort
   Else
   MsgBox "Already at the top."
   End If
   -->
   </SCRIPT>
   

8. Add code to the onclick event of the ImgDown control to move the current record below the next record and update the RecordPosition field.

   Sample onclick event procedure of the ImgDown control

   <SCRIPT language=vbscript event=onclick for=imgDown>
   <!--
   Dim sect
   Dim sect2
   Dim rs
   Dim iOrder
   
   Set sect = MSODSC.CurrentSection
   Set sect2 = sect.NextSibling

   ' If record is not already at the bottom, do this.
   If (sect2.type = msodsc.constants.sectTypeHeader) Then
   iOrder = CLng(sect.HTMLContainer.children("RecordPosition").innerText)
   Set rs = sect.DataPage.Recordset

   ' Update the RecordPosition fields.
   rs.Fields("RecordPosition").Value = iOrder + 1
   rs.UpdateBatch
   sect2.MakeCurrent
   rs.Fields("RecordPosition").Value = iOrder
   rs.UpdateBatch

   rs.Sort = sect.DataPage.Recordset.Sort
   Else
   MsgBox "Already at the bottom."
   End If
   -->
   </SCRIPT>
   

9. Add the following lines of code in a separate script block, as shown here.

   <SCRIPT language=vbs>
   
   ' Globals for tracking the up/down buttons
   Dim elemOver
   Dim elemSectOver
   Dim elemImgUp
   Dim elemImgDown
   
   Set elemOver = Nothing
   Set elemSectOver = Nothing
   Set elemImgUp = Nothing
   Set elemImgDown = Nothing
   </SCRIPT>

For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).