8.5 Sorting records

Sorting is the process of arranging records into a particular order. You are able to arrange records into alphabetical, numerical or chronological (date) orders.

You can sort data on one field only or on multiple fields. For example, you could list the Pizza Palace database in location order and within each location list the employees in alphabetical order by the names.

Databases exercise 3

Single field sorting

Skills practised
  • One field sort
  • Multiple fields sort
  • Sort ascending
  • Sort descending

Sorting can be quickly carried out on single fields.

  1. In Microsoft Access open the EMPLOYEE PAY QUERY and:
    1. Click in a LOCATION value then click on the ASCENDING icon in the Home tab of the ribbon and all the records are rearranged so that they are in LOCATION order.
    2. Click on a PAY value then click on the DESCENDING icon in the Home tab of the ribbon and all the records are rearranged so that the highest incomes are listed first down to the lowest incomes.
    3. Click on the REMOVE SORT icon in the ribbon to return the records to their original order.
  2. In FileMaker Pro:
    1. Click on the LOCATION field label at the top of the table and the records are rearranged so that they are displayed alphabetically in location order.
    2. Click on the PAY heading and all the records are rearranged so that the lowest incomes are listed first down to the highest incomes.
    3. Click on the PAY heading again and all the records are rearranged in descending order.
    4. Click on the SORT icon in the Toolbar, select UNSORT in the SORT RECORDS dialogue box and select OK to return the records to their original order.

Multiple field sorting

More complex sorting can be carried out on multiple fields. To illustrate this the data will be sorted into LOCATION alphabetical order, within each location the LAST NAMES will be sorted into alphabetical order and, if any last names are the same, the FIRST NAMES will be sorted into alphabetical order.

  1. In Microsoft Access a query can be rearranged to permanently display a sort order. To do this the columns are moved in the query so that the first field to be sorted is at the left, followed by the next field to be sorted, etc.
    1. Click on the VIEW icon in the ribbon to open the QUERY DESIGN view.
    2. Click on the bar above the LOCATION field then drag the column by the bar to the left of FIRST NAME.
    3. Click on the bar above the LAST NAME field then drag the column by the bar to the left of FIRST NAME.
    4. Set the SORT box for LOCATION, LAST NAME and FIRST NAME to ASCENDING.
      TS0808.png
    5. Run the Query and the records should be grouped with the LOCATIONS together and within each location the names in alphabetical order.
    6. If you don’t want a query altered, you can duplicate then make the sort changes to the duplicate, or you can create a new query.
  2. In FileMaker Pro:
    1. Click on the SORT icon in the Toolbar.
    2. Click on the CLEAR ALL button to clear any fields from the SORT ORDER frame.
    3. Move the LOCATION, LAST NAME and FIRST NAME fields into the SORT ORDER frame.
    4. If necessary a field can be selected in the SORT ORDER frame and its order changed to DESCENDING. In this case the default ASCENDING ORDER is applied to all the fields.
      TS0809.png
    5. Click on the SORT button and the records should be grouped with the LOCATIONS together and within each location the names in alphabetical order.