MS Access —

Sorting a Query Using Multiple Fields

Creating a sort that sorts by LastName, then by FirstName.

Tables normally display records in the order they were entered. Instead of working with a table's jumbled record order, you can create a simple query that sorts the table information and presents it in an ordered, easy-to-read display. You can sort records alphabetically, numerically, or chronologically (by date) in ascending (A to Z) or descending (Z to A) order. You can also sort by multiple fieldsfor example, you could sort by LastName and then by FirstName. This lesson will show you how you can use a query to sort information in a table.

  1. In the Database window, double-click Create query from Design view. Click Employees, click Add, and then click Close.

    Notice that an asterisk (*) appears at the top of the Employees field list. Selecting a table's asterisk (*) in a query selects all of its fields at once.

  2. Double-click the asterisk (*) in the Employees field list.

    Access adds the asterisk to the design grid. Now you have to add the fields you want to use to sort the query.

  3. Double-click LastName and FirstName in the Employees field list to add them to the design grid.

    You are going to use the LastName and FirstName fields to sort the query. To sort a query, click the Sort row for the field you want to use to sort the query and select either Ascending or Descending.

  4. Click in the LastName field's Sort box. Click the list arrow and select Ascending from the list. Repeat this step for the FirstName field.

    The LastName and FirstName fields will already be displayed with all the other fields in the table because you added the asterisk (*) to the design grid. Because you don't want the LastName and FirstName fields to appear twice, you can uncheck their "Show" boxes.

  5. Uncheck the Show box for the LastName and FirstName fields.

    Your query should look like figure. The query will still use the LastName and FirstName fields for sorting purposes, but it won't display these field names in the query results.

  6. Save your query as AZ Query and then click the Run button on the toolbar to view the results. Close the query when you're finished.

TO SORT A QUERY USING MULTIPLE FIELDS:

  1. OPEN/DISPLAY THE QUERY IN DESIGN VIEW.

  2. IF NECESSARY, ADD THE FIELD YOU WANT TO USE TO SORT THE QUERY TO THE DESIGN GRID.

  3. CLICK THE SORT BOX LIST ARROW FOR THE FIRST FIELD YOU WANT TO USE TO SORT THE QUERY, THEN SELECT A SORT ORDER.

  4. REPEAT STEPS 2-3 FOR EACH ADDITIONAL FIELD YOU WANT TO USE TO SORT THE QUERY, BEARING IN MIND THAT THE FIELDS WILL BE SORTED FROM LEFT TO RIGHT.