What does a sort tool enable you to do in a database?

Lesson vii: Sorting and Filtering Records

/en/access2016/working-with-forms/content/

Introduction

Access gives you the power to piece of work with enormous amounts of data, which means it tin be difficult to learn anything about your database only past glancing at it. Sorting and filtering are two tools that let you lot customize how you organize and view your data, making information technology more convenient to work with. In this lesson, yous'll learn how to sort and filter records.

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

Scout the video below to learn more about sorting and filtering records in Admission.

Well-nigh sorting and filtering

Essentially, sorting and filtering are tools that let you organize your information. When you sort data, y'all are putting it in lodge. Filtering information lets you hide unimportant data and focus only on the data you're interested in.

Sorting records

When y'all sort records, you are putting them into a logical social club, with similar data grouped together. As a result, sorted information is ofttimes simpler to read and understand than unsorted information. By default, Access sorts records past their ID numbers. Even so, there are many other means records can exist 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 past the terminal proper name of the customers who placed the orders.
• Customers could be sorted by proper noun or by the urban center or null code where they live.
• Products could be sorted by name, category (like pies, cakes, and cupcakes), or toll.

You lot can sort both text and numbers in 2 ways: in ascending social club and descending order. Ascending means going upwards, so an ascending sort will accommodate numbers from smallest to largest and text from A to Z. Descending means going downwards, 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 everyman ID numbers appear first.

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

To sort records:

1. Select a field you want to sort past. In this instance, we will sort by customers' last names.

   

2. Click the Home tab on the Ribbon, and locate the Sort & Filter group.
3. Sort the field by selecting the Ascending or Descending control.

   

4. The table will now be sorted by the selected field.

   

5. To save the new sort, click the Salve command on the Quick Access Toolbar.

   

Afterward y'all save the sort, the records will stay sorted this way until y'all perform another sort or remove the electric current one. To remove a sort, click the Remove Sort command.

Filtering records

F ilters let you to view just the data y'all want to encounter. When you create a filter, y'all set up criteria for the information y'all want to display. The filter then searches all of the records in the tabular array, finds the ones that meet your search criteria, and temporarily hides the ones that don't.

Filters are useful because they allow y'all 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 club information, you could create a filter to display merely customers living within a certain city or but orders containing a sure product. Viewing this data with a filter would be far more convenient than searching for it in a large tabular array.

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

To create a unproblematic filter:

1. Click the drib-down arrow next to the field yous want to filter by. We will filter by city because we want to run across a list of customers who live in a certain city.

   

2. A drop-down card with a checklist volition announced. Only checked items volition exist included in the filtered results. Clicking Select All will select or deselect everything at once. In our example, we'll deselect everything except Cary.

   

3. Click OK. The filter volition be applied. Our customers tabular array now displays simply customers who live in Cary.

   

Toggling your filter allows y'all to plough it on and off. To view the records without the filter, click the Toggle Filter command. To restore the filter, click it once again.

Creating a filter from a selection

Filtering by choice allows yous to select specific data from your table and observe data that is similar or different to it. For case, if y'all were working with a bakery's database and wanted to search for all products whose names contained the word chocolate, you lot could select that word in one product name and create a filter with that option. Creating a filter with a option tin exist more convenient than setting up a uncomplicated filter if the field you're working with contains many items. You tin choose from the following options:

• Contains includes merely records with cells that contain the selected data.
• Does Not Contain includes all records except for those with cells that comprise the selected data.
• Ends With includes only records whose data for the selected field ends with the search term.
• Does Not Cease With includes all records except for those whose data for the selected field ends with the search term.

To create a filter from a selection:

1. Select the prison cell or data y'all desire 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 Production Name field.

   

2. Select the Home tab on the Ribbon, locate the Sort & Filter group, and click the Pick driblet-downwardly arrow.

   

3. Select the type of filter you lot want to apply. We'll select Contains "Chocolate" considering we want to see records that incorporate the word Chocolate anywhere in the field.

   

4. The filter volition be applied. Our tabular array now displays only products with the word Chocolate in their names.

   

Creating a filter from a search term

Y'all tin can also create a filter by entering a search term and specifying the way Admission should match data to that term. Creating a filter from a search term is like to creating a filter from a pick.

Filtering text by a search term

When filtering text by entering a search term, you tin use some of the aforementioned options you use when filtering by a pick, similar Contains, Does Non Incorporate, Ends With, and Does Not End With. You lot tin besides choose from the post-obit 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 information for the selected field begins with the search term
• Does Not Begin With, which includes all records except for those whose information for the selected field begins with the search term

To filter text by a search term:

1. Click the driblet-down arrow next to the field you want to filter past. We want to filter the records in our orders table to display only those that contain notes with sure information, and so we'll click the arrow in the Notes field.

   

2. In the drop-down bill of fare, hover your mouse over Text Filters. From the listing that appears, select the way you want the filter to lucifer the term you enter. In this example, we want to view only records whose notes betoken the order was placed for a party. We'll select Contains and then we tin search for records that contain the word party.

   

3. The Custom Filter dialog box will appear. Blazon the word you want to utilize in your filter.

   

4. Click OK. The filter will be applied.

   

Filtering numbers with a search term

The process for filtering numbers with a search term is similar to the process for filtering text. However, unlike filtering options are available to you when working with numbers. In addition to Equals and Does not Equal, you tin 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 autumn inside a certain range

To filter numbers by a search term:

1. Click the driblet-downward arrow next to the field you lot desire to filter by. We want to filter the records in our menu items table by cost, and then we'll click the arrow in the Toll field.
2. In the drop-down menu, hover your mouse over Number Filters. From the list that appears, select the mode you want the filter to match your search term. In this example, nosotros want to see items that are less than $5, so we'll select Less Than.

   

3. The Custom Filter dialog box volition appear. Type the number or numbers y'all want to use in your filter. We'll blazon 5 so the filter volition show usa only card items that cost $5 or less.

   

4. Click OK. The filter will exist applied.

   

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 past periods of time.

Challenge!

1. Open up our practice 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 number filter that only shows numbers greater than or equal to five.
5. Utilise an ascending sort to the Sum of Quantity field.

/en/access2016/designing-a-uncomplicated-query/content/

carneymasul1982.blogspot.com

Source: https://edu.gcfglobal.org/en/access2016/sorting-and-filtering-records/1/

Share this post