Sorting Arranges Records In A Table Only By The Due Date Of The Assignment

Introduction

Several of the lists you see in day-to-day life are either already sorted or allow you to sort them the way you want. A calendar or a dayplanner is sorted on date. On the other hand, when you search for flight tickets or browse the entries in your e-mail inbox, you have the option of sorting on one of many available fields. You can sort tickets based on price, departure time, or the airline, and you can sort the entries in your mail box by sender, subject, or date. A sorted list helps users review and locate what they want without having to browse the data.

You can sort the records in a table, query, form, or a report on one or more fields. With minimal design work, you and the users of your form or report can choose how the records are sorted.

For example, when you design the product catalog report, you might choose to list the products in alphabetical order. The order you choose when you design an object becomes the object's default sort order. But when viewing the query or report, each of your users can sort the records the way they want, such as by price or by supplier.

Note: A view can be sorted on any field that is displayed in the view, except on fields that contain attachments or OLE objects.

You can fine-tune your results by sorting records on more than one field. When sorting on multiple fields, it is important to identify what are known as the outermost and innermost sort fields. Designating the appropriate fields as inner and outer sort fields is necessary to get the results you want. As an example, suppose you want to sort the Contacts table on the FirstName and LastName fields. If you want the first names sorted from A to Z (or Z to A) within each last name, FirstName is the innermost field. On the other hand, if you want the last names sorted within each first name, LastName is the innermost field. Put another way, the records are sorted first (outermost) on the LastName field and then next (innermost) on the FirstName field.

1. LastName is the outermost field and FirstName is the innermost field

2. FirstName is the outermost field and LastName is the innermost field

One thing to remember when applying sort orders is that numbers, text, and special characters are sorted according to the selected language and regional settings of your computer. If the language and regional settings specified in the Access Options dialog box do not match those specified in Control Panel, the resulting sort orders might not match your expectations.

Review or change the default language or region

To review the language settings in Access, click the Microsoft Office Button , and then click Access Options. Under Set Up, review or change the value in the New database sort order list box. Set the option to General if you want to use one of these languages — Afrikaans, Albanian, Arabic, Basque (Basque), Bulgarian, Belarusian, Catalan, Dutch, English, Faeroese, Farsi, German-Standard, Greek, Hebrew, Hindi, Indonesian, Italian, Malay, Portuguese, Russian, Serbian, Swahili, and Urdu. Note that this setting affects only new databases. To apply this setting to an existing database, first compact the database.

To compact the database:

  • Click the Microsoft Office Button , point to Manage, and then, under Manage this database, click Compact and Repair Database.

To review the regional settings for your computer, open Regional and Language Options in Control Panel. On the Regional Options tab, review and make the changes you want. For more information, see Microsoft Windows Help.

Top of Page

Sort the records in a view

Note: In Access, sorting a report is slightly different from sorting a table, query, or form.

Sort a report

Sort a table, query, or form

Sort a report

  1. Open a report in Layout view or Design view.

  2. If the Group, Sort, and Total pane is not open, in the Grouping & Totals group (on the Format tab in Layout view, or on the Design tab in Design view), click Group & Sort.

  3. Do one of the following:

    • If the report is neither grouped nor sorted, click Add a group in the Group, Sort, and Total pane, and then select the outermost sort field. Click the second drop-down arrow and choose how the values should be ordered. Click More, then click the arrow to the right of with a header section, and select without a header section. Repeat these steps for each sort field, with the innermost sort field appearing at the bottom.

    • If the report is already grouped or sorted, review the existing levels and make changes carefully. To change a field, use the first drop-down arrow. To change the order, click the second drop-down arrow. To change a grouping level to just a sorting level, change with a header section to without a header section.

Tip: To sort a report on a single field, open the report in Layout view, right-click the field, and select the sort command you want. For example, if you select the Age field, click either Sort Smallest to Largest or Sort Largest to Smallest. But if the report is grouped or if you want to sort on multiple fields, you must use the Group, Sort, and Total pane.

Sort a table, query, or form

  1. Identify the fields on which you want to sort. To sort on two or more fields, identify the fields that will act as the innermost and outermost sort fields.

  2. Right-click anywhere in the column or control corresponding to the innermost field, and click one of the sort commands. The commands vary with the type of data that is in the selected field.

    Note: When sorting data for a field that is of the Yes/No data type, a value of "Yes", "True" or "On" is considered "Selected", and a value of "No", "False" or "Off" is considered "Cleared". The default display for this type of field is a check box, but you can set it to display as a text box or a combo box. If you change the display for this type of field to text box or combo box, it still sorts as "Selected" or "Cleared".

    1. Number, Currency, AutoNumber

    2. Text, Memo, Hyperlink

    3. Yes/No

    4. Date/Time

  3. Repeat the previous step for each sort field, ending with the outermost sort field.

    The records are rearranged to match the sort order.

    Note: If a text field contains null and zero-length strings, when sorted in ascending order, the records with null values are listed first, then the records with zero-length strings, and then the records with nonblank values.

    Learn about how special characters, leading spaces, and negative values are handled during sorting

    If a value in a field starts with a special character — such as a hyphen, parenthesis, or other symbol — you will observe the following behavior when sorting in ascending order:

    • Values that start with a space character will appear before alphanumeric values.

    • Values enclosed in quotation marks follow values that start with a space, but precede alphanumeric values.

    • Values that start with the minus sign (-) precede values that start with the plus (+) sign.

    • For all other symbols, the order is determined by looking at the ASCII character codes of the characters. For example, the code for the dollar symbol ($) is 36, and the code for the equal sign (=) is 61, so values starting with $ will appear before values that start with =.

      To override this order, you can choose to ignore the first character of all the values in the field. This technique works if the values in the field always start with a single special character, such as a minus sign, or with the same number of special characters — this way, you know how many characters to ignore. For step-by-step information about how to sort on partial values, see the article Sort records based on partial values in a field. If the number of characters to ignore varies, you can specify a custom sort order. For step-by-step information, see the article Sort records in custom order.

Remember that you cannot remove a sort order from just a single field. To remove sorting from all sort fields, on the Home tab, in the Sort & Filter group, click Clear All Sorts, and then apply the sort orders you want.

Additional sorting scenarios

When you use the sort commands, the data type of the field and the complete values of each field are used to determine the order of records. But what if you want text values, such as Monday, Tuesday, and so on, sorted in custom order and not in alphabetical order? Or, you want to sort a text field that contains IP addresses? See the following topics if your sorting needs are not met by the sort commands:

  • Sort records based on partial values in a field Want to sort records based on the first few or last few characters in a field? If you are comfortable writing simple expressions, see this topic that explains how to use expressions to extract partial values.

  • Sort records in case-sensitive order Access ignores the case of text values. For example, the values smith and Smith will be treated as exactly the same, and you cannot control which one will appear first. See this topic if you want to do case-sensitive sorting.

  • Sort records on numeric values stored in a text field Do you see records sorted on the individual digits and not on the numeric value? If, for example, records with values 11 and 121 appear before records with values 2 or 25, see this article.

  • Sort records in custom order If you want to sort certain values, such as names of days (Monday, Tuesday, and so on) or employee titles (Manager, Director) in an order that is meaningful to you, see this article.

  • Sort IP addresses A field that stores IP addresses contains multiple numbers and periods (.), and so a simple text or numeric sort will not work for IP addresses. See this topic to learn how to sort IP addresses.

Top of Page

Remove a sort order

  • To remove a sort order from a table, query, or form, on the Home tab, in the Sort & Filter group, click Clear All Sorts.

    This will remove the sort order from all fields in the view.

  • To remove a sort order from a report, open the Group, Sort, and Total pane by clicking Group & Sort in the Grouping & Totals group (on the Format tab in Layout view, on the Design tab in Design view). Then, click the sorting level that you want to remove, and click Delete. An entry corresponding to a sort field will start with the words Sort by. Avoid deleting entries that start with Group by, because that will change the design of the report.

Top of Page

Save a sort order with an object

The last-applied sort order is automatically saved with the table, query, form, or report. If you want it automatically applied the next time you open the object, make sure the OrderByOnLoad property of the object is set to Yes.

Since the last-applied sort order will change from time to time, you might want to define a permanent (or default) sort order. You can specify a default sort order for a query or report. An important thing to note is that although the default sort order is not overwritten by the current or last-saved sort order, it comes into effect only when the current or last-saved sort order is removed from the view.

  • To define a default sort order for a query, open it in Design view. Add the sort fields to the design grid and, in the Sort row, specify the order you want.

  • To define a default sort order for report, use the Group, Sort, and Total pane in Layout or Design view. For step-by-step information, see the section Sort a report.

To see the records sorted in default sort order, on the Home tab, in the Sort & Filter group, click Clear All Sorts. This removes the current or last-saved filter from the view, and lets you view the records in default order.

Top of Page

When working in Access sort data to make it easier to work with. In Access filter data as well so you see only what you want to see.

Introduction

Video: Sorting and Filtering

Watch the video (4:07). Need help?

Access gives you the ability to work with enormous amounts of data, which means it can be difficult to learn anything about your database just by glancing at it. Sorting and filtering are two tools that let you customize how you organize and view your data, making it more convenient to work with. In this lesson, you'll learn how to sort and filter records.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2013 sample database. You will need to have Access 2013 installed on your computer in order to open the example.

About sorting and filtering

Essentially, sorting and filtering are tools that let you organizeyourdata. When you sort data, you are putting it in order. Filtering data lets you hide unimportant data and focus only on the data you're interested in.

Sorting records

When you sort records, you are putting them into a logical order, with similar data grouped together. As a result, sorted data is often simpler to read and understand than unsorted data. By default, Access sorts records by their ID numbers. However, there are many other ways records can be sorted. For example, the information in a database belonging to a bakery could be sorted in a number of ways:

  • Orders could be sorted by order date or by the last name of the customers who placed the orders.
  • Customers could be sorted by name or by the city or zip code where they live.
  • Products could be sorted by name, category (like pies, cakes, and cupcakes), or price.

You can sort both text and numbers in two ways: in ascending order and descending order. Ascending means going up, so an ascending sort will arrange numbers from smallest to largest and text from A to Z. Descending means goingdown, or largest to smallest for numbers and Z to A for text. The default ID number sort that appears in your tables is an ascending sort, which is why the lowest ID numbers appear first.

In our example, we will be performing a sort on a table. However, you can sort records in any Access object. The procedure is largely the same.

To sort records:

  1. Select a field you want to sort by. In this example, we will sort by customers' last names.
    Selecting a field by clicking on its title
  2. Click the Home tab on the Ribbon, and locate the Sort & Filter group.
  3. Sort the field by selecting the Ascending or Descending command.
    • Select Ascending to sort text A to Z or to sort numbers from smallest to largest. We will select this in our example because we want the last names to be in A-to-Z order.
    • Select Descending to sort text Z to A or to sort numbers from largest to smallest.
    The Ascending and Descending Sort commands
  4. The table will now be sorted by the selected field.
    The same table with an ascending sort applied to the Last Name field
  5. To save the new sort, click the Save command on the Quick Access toolbar.
    Saving the sort

After you save the sort, the records will stay sorted this way until you perform another sort or remove the current one. To remove a sort, click the Remove Sort command.

Removing the sort

Filtering records

Filters allow you to view only the data you want to see. When you create a filter, you set criteria for the data you want to display. The filter then searches all of the records in the table, finds the ones that meet your search criteria, and temporarily hides the ones that don't.

Filters are useful because they allow you to focus in on specific records without being distracted by the data you're uninterested in. For instance, if you had a database that included customer and order information, you could create a filter to display only customers living within a certain city or only orders containing a certain product. Viewing this data with a filter would be far more convenient than searching for it in a large table.

In our examples and explanations, we will be applying filters to tables. However, you can apply filters to any Access object. The procedure is largely the same.

To create a simple filter:

  1. Click the drop-down arrow next to the field you want to filter by. We will filter by city because we want to see a list of customers who live in a certain city.
    Selecting a field to sort by
  2. A drop-down menu with a checklist will appear. Only checked items will be included in the filtered results. Use the following options to determine which items will be included in your filter:
    • Select and deselect items one at a time by clicking their checkboxes.
    • Click Select All to include every item in the filter. If all items are already selected, this option will deselect all items.
    • Click Blanks to set the filter to find only the records with no data in the selected field.
    Setting the filter to only show records with "Cary" in the city field
  3. Click OK. The filter will be applied. Our customers table now displays only customers who live in Cary.
    The filtered table, now showing only the records for customers who live in Cary

Toggling your filter allows you to turn it on and off. To view the records without the filter, click the Toggle Filter command. To restore the filter, click it again.

Removing the current filter with the Toggle Filter command

Creating a filter from a selection

Filtering by selection allows you to select specific data from your table and find data that is similar or dissimilar to it. For instance, if you were working with a bakery's database and wanted to search for all products whose names contained the word chocolate, you could select that word in one product name and create a filter with that selection. Creating a filter with a selection can be more convenient than setting up a simple filter if the field you're working with contains many items.

To create a filter from a selection:

  1. Select the cell or data you want to create a filter with. We want to see a list of all of our products that contain the word chocolate in their names, so we'll select the word chocolate in the Product Name field.
    Selecting text to filter for
  2. Select the Home tab on the Ribbon, locate the Sort & Filter group, and click the Selection drop-down arrow.
    Clicking the Filter by Selection command
  3. Select the type of filter you want to apply:
    • Contains includes only records with cells that contain the selected data. We'll select this because we want to see records that contain the word chocolate anywhere in the title.
    • Does Not Contain includes all records except for those with cells that contain the selected data.
    • Ends With includes only records whose data for the selected field ends with the search term.
    • Does Not End With includes all records except for those whose data for the selected field ends with the search term.
    Setting the filter to show only records that contain the selected word
  4. The filter will be applied. Our table now displays only products with the word chocolate in their names.
    The filtered table showing only records containing "chocolate" in the Product Name field

Creating a filter from a search term

You can also create a filter by entering a search term and specifying the way Access should match data to that term. Creating a filter from a search term is similar to creating a filter from a selection.

Filtering text by a search term

When filtering text by entering a search term, you can use some of the same options you use when filtering by a selection, like Contains, Does Not Contain, Ends With, and Does Not End With. You can also choose from the following options:

  • Equals, which includes only records with data that is identical to the selected data
  • Does Not Equal, which includes all records except for the data that is identical to the selection
  • Begins With, which includes only records whose data for the selected field begins with the search term
  • Does Not Begin With, which includes all records except for those whose data for the selected field begins with the search term

To filter text by a search term:

  1. Click the drop-down arrow next to the field you want to filter by. We want to filter the records in our orders table to display only those that contain notes with certain information, so we'll click the arrow in the Notes field.
    Selecting a field to filter by
  2. In the drop-down menu, hover your mouse over Text Filters. From the list that appears, select the way you want the filter to match the term you enter. In this example, we want to view only records whose notes indicate the order was placed for a party. We'll select Contains so we can search for records that contain the word party.
    Selecting filter settings
  3. The Custom Filter dialog box will appear. Type the word you want to use in your filter.
    Typing the term the filter will search for
  4. Click OK. The filter will be applied.
    The filtered table showing only records containing the word "party" in the Notes field

Filtering numbers with a search term

The process for filtering numbers with a search term is similar to the process for filtering text. However, different filtering options are available to you when working with numbers. In addition to Equals and Does not Equal, you can choose:

  • Greater Than to include only records with numbers in that field that are greater than or equal to the number you enter
  • Less Than to include only records with numbers in that field that are less than or equal to the number you enter
  • Between to include records with numbers that fall within a certain range

To filter numbers by a search term:

  1. Click the drop-down arrow next to the field you want to filter by. We want to filter the records in our menu items table by price, so we'll click the arrow in the Price field.
  2. In the drop-down menu, hover your mouse over Number Filters. From the list that appears, select the way you want the filter to match your search term. In this example, we want to see items that are less than $5, so we'll select Less Than.
    Selecting a field to filter by
  3. The Custom Filter dialog box will appear. Type the number or numbers you want to use in your filter. We'll type 5 so the filter will show us only menu items that cost $5 or less.
    Typing the number to filter for
  4. Click OK. The filter will be applied.
    The filtered table showing only records for menu items costing $5 or less

Specific types of numbers may include other filtering options. For instance, dates stored in numerical form (mm/dd/yyyy, or 12/01/2013) include options to filter by periods of time.

Filtering by date

Challenge!

  1. Open an existing Access database. If you want, you can use our Access 2013 sample database.
  2. Open the query called Cakes and Pies Sold.
  3. Apply a filter to the Product Types field that shows only Cakes.
  4. In the Sum of Quantity field, apply a filter that only shows numbers greater than five.
  5. Apply an ascending sort to the Sum of Quantity field.
  6. Save the database.

0 comments

Leave a Reply

Your email address will not be published. Required fields are marked *