Introduction to Filtering Records

Fundamentals of Filtering Records

Introduction

Data analysis consists not only of selecting records but also of setting some conditions by which the records should be presented. A condition is set as if using a funnel that decides what to let through and what to exclude. The condition or rule is also called a criterion.

Data analysis consists not only of selecting records but also of setting some conditions by which the records should be presented. A condition is set as if using a funnel that decides what to let through and what to exclude. The condition or rule is also called a criterion. A filter is a criterion or a set of criteria that must be applied to a set to create a list of records that abide by a common rule. Filters can be used to isolate records on a table, a query, a form, or a report. There are differences on the way each type of value handles a filter.

Practical Learning: Introducing Records Filtering

1. Start Microsoft Access
2. On the list of fields, click StatesStatistics3 from the previous lesson
3. In the Navigation Pane, double-click the States table
4. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)

   

Filtering Records

Microsoft Access provides various techniques to visually filter records. As we will see in future sections and lessons, to filter records, you will right-click the list or use buttons in the Sort & Filter section in the Home tab of the Ribbon.

Removing a Filter

When a table, a query, a form, or a report is filtered, in the Sort & Filter section of the Ribbon, the Toggle Filter button is highlighted . Also, the bottom section of the table, query, or form displays a Filtered button . If you perform another filter on the list, only the selected records would be considered. This means that you must decide whether you want the new filtering to apply to all records or only to the new ones. If you want to use all records of the list, you must first remove the previous filter.

To dismiss the previous filtering operation:

• In the Sort & Filter section of the Ribbon, if the window is large enough to show the Toggle Filter button , then click it. If the width used by Microsoft Access is not large enough, then the Sort & Filter section would be equipped with the Remove Filter button . To remove the filtering, you can click that button
• Right-click the table, query, or form and click Clear Filter From ...
• Click the down-pointing button on the right side of the column name and click the Clear Filter From option. Here is an example:

  

• In the bottom section of the table, query, or form, click the Filtered button

Saving Data Analysis Results

In this and the next lessons, we will see different ways to examine the values stored in a database. Every time you finish analyzing data, you can dismiss the result or save it. If you don't save the result, all the analysis will be lost. The best way to save the results of data analysis is by saving them in a query. Besides the ability to save sorted and/or filtered lists, queries provide advanced techniques of performing data analysis. Because they use the SQL, they use a syntax that is not directly available to tables, forms, and reports. In fact, queries provide a good alternative to creating the record source that can be used to populate forms and reports.

Introduction to Filtering by Selection

Overview

Imagine you have a list where some records share a certain value. Based on such a field, you can retrieve a list that includes only the particular string, that is, records that share that value. This is referred to as as filtering by selection.

Filtering by Selection for Equality

To get a list of all records that hold the exact same value as that of your choice, in the Datasheet View of a table, the Datasheet View of a Query, the Datasheet View of a form, the Form View of a form, the Report View or the Layout View of a report:

• Right-click the value in the column and click the Equals option
• Click the value. In the Sort & Filter section of the Ribbon, click the Selection button . From the menu that appears, click the Equals option

After clicking, the object will display only the records that share the specified in the field.

Practical Learning: Filtering for Equality

1. To see only the states in New England, in the Region column, right-click New England and click Equals "New England"

   

   

2. On the table, right-click New England and click Clear Filter From Property Type

   

Filtering Records by Design

Another way to filter records is while you are designing a table, a query, a form or a report. In order to see the results for a table, you must first save it. If you save the table like that, the filtering feature would be included in the table.

To filter records in design, display the object in Design View. Use the Property Sheet of the table, use the bottom part of the field that will use the filter for a query, user the Record Source for the form or report.

Filtering for Un-Equal Values

To filter for records that are not equal to a certain value, in a table, a query, a form, or a report:

• To use an exact value:
  • Right-click the cell or text box that contains the exact value and click the Does Not Equal option
  • Click the cell or text box that contains the value or exact string. In the Sort & Filter section of the Ribbon, click the Selection button and click the Does Not Equal option
• To avoid matching a value, select a letter or a sub-string in the column of your choice:
  • Right-click the value in the column and click the Equals option
  • Click the value. In the Sort & Filter section of the Ribbon, click the Selection button . From the menu that appears, click the Equals option

Practical Learning: Filtering for Un-Equal Sub-Strings

1. To see the states outside of New England, in the Region column, right-click New England and click Does Not Equals "New England"

   

   

2. In the bottom section of the table, click the Filtered button
3. Close the table without saving it
4. Open the Altair Realtors2 database from the previous lesson
5. In the Navigation Pane, right-click the Properties table and click Copy
6. Right-click an unoccupied area of the Navigation Pane and click Paste
7. Set the name to Ready for Sale and click OK
8. In the Navigation Pane, double-click the Ready for Sale table
9. In the Home tab of the Ribbon, change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Font Color: Orange, Accent 2, Lighter 80% (Theme Colors: 6th column, 2nd row)
   Background Color: Dark Red (Standard Colors: 1st column, 7th row)
   Alternate Row Color: Maroon 4 (Standard Colors: 6th column, 5th row)

   

Filtering by Selection and Type

Filtering by Selection for Numbers

If the data type of the field by which you want to filter records is number-based, you can find records that use the same value or those that use different values. You can also find records that use a higher or lower value. To make your selection, right-click the value or use the Selection button of the Ribbon as we saw earlier.

Practical Learning: Filtering by Selection for Numbers

1. In the Bedrooms column, right-click 4 and click Equals 4

   

2. Click the button on the right side of Bedrooms and click Clear Filter From Bedrooms
3. In the Market Value column, right-click $555,540 and click Less Than or Equal To $555,540.00

   

   

4. In the bottom side of the table, click the Filtered button

Filtering by Selection for Date/Time Values

If the data type of the field by which you want to filter records is a date or time-based, you can find records that occur:

• On the same date or time
• On the same date or time or before that date or time
• On the same date or time or after that date or time
• On different days or times

Practical Learning: Filtering by Selection for Date/Time Values

1. Open the StatesStatistics3 database used earlier
2. In the Navigation Pane, right-click the Summary report and click Layout View
3. On the report, find one of the Dakotas (north or south) and right-click its Date record

   

4. Click the Equals option:

   

5. On the Ribbon, click the Toggle Sort button
6. On the report, right-click the Date value for Maine and click On Or Before 3/15/1820

   

   

7. Close the report without saving it

Filtering by Selection for Boolean Values

If a field uses Yes/No values or check boxes in a form or report, you can get all records that are true (or checked) or all those that are false (or unchecked/cleared).

Practical Learning: Filtering by Selection for Boolean Values

1. Open the Altair Realtors2 database used earlier
2. In the Navigation Pane, double-click the Properties table
3. In the Home tab of the Ribbon, change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Font Color: Orange, Accent 2, Lighter 80% (Theme Colors: 6th column, 2nd row)
   Background Color: Dark Red (Standard Colors: 1st column, 7th row)
   Alternate Row Color: Maroon 4 (Standard Colors: 6th column, 5th row)
4. Right-click a checkd box in the Basement column

   

5. Click IsSelected

   

6. In the bottom side of the table, click the Filtered button
7. Right-click an unchecked box in the Indoor column and click Is Not Selected

   

8. In the bottom side of the table, click the Filtered button

Filtering by Selection for Null Records

If a field is missing some values, you can find records that are empty or those that contain values.

Practical Learning: Filtering by Selection for Null Records

1. Right-click an empty cell below State and click Equals Blank

   

   

2. In the bottom side of the table, click the Filtered button
3. Close the table without saving it

Strings and Filtering by Selection

Introdution

If you have a set of records with a string-based column, you may want to get a list of records that share a detail inside the string, also called a sub-string. To exercise an advanced level of control on the sub-string or the combination of (sub) strings, right-click any field under the column, position the mouse on Text Filters and choose one of the options. The Custom Filter dialog box would come up. When using the Custom Filter dialog box, you can type an exact string or use some characters to create an approximate string.

Filtering by a Contained Sub-String

To filter by an exact sub-string that is contained in a string, first select the sub-string. Then:

• Right-click the selected sub-string and click the Contains option
• In the Sort & Filter section of the Ribbon, you can click Selection, and click the Contains option

When using the Contains option, you must first select a string or a sub-string. In some cases, you may want the string to contain one or more characters or sub-strings.

Practical Learning: Introducing Filtering by a Contained Sub-String

1. Open the StatesStatistics3 database used earlier
2. In the Navigation Pane, double-click the States table
3. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)
4. On the table, in the State column, select nn in Connecticut
5. To get a list of states that include two consecutive Ns in their name, right-click the nn selected and click Contains "nn":

   

   

6. Close the table
7. When asked whether you want to save, click No
8. In the Navigation Pane, double-click the Summary form
9. In the Region column of the table, select North
10. In the Sort & Filter section of the Ribbon, click Selection and click Contains North

    

    

11. Close the form

Filtering by an Equal Sub-String

To select records that exactly match a string, in a Datasheet View, a Form View, a Report View, or a Layout View:

• Right-click the value in the column and click the Equals option
• Click the value. In the Sort & Filter section of the Ribbon, click the Selection button . From the menu that appears, click the Equals option

Alternatively, right-click the string in the column, position te mouse on Text Filters, and click Equals... In the Custom Filter dialog box, you can type the exact string or an approximate string to look for:

• If you type an exact string, this would be the same as the Equals option we saw above except that the dialog box allows you to enter a string other than the one you would have selected from the column. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string, not part of it. For example, if you type south, only records that have exactly South and only South in the column would be selected for the result
• If you don't know the beginning of a string, you can precede a character or a sub-string with the * symbol. For example, if you type *ar*, all entries that include the sub-string ar would be included in the list. Examples would be Farms, Edwards, Bartlett

Practical Learning: Filtering by an Equal Sub-String

1. In the Navigation Pane, double-click the States table
2. Right-click anything below Region, position the mouse on Text Fields, and click Equals...
3. To see only the states in the Pacific region, in the Customer Filter dialog box, type Pacific

   

4. Click OK

   

5. Right-click any cell below Region and click Clear Filter From Region
6. Right-click any cell below State, position the mouse on Text Fields, and click Equals...
7. In the dialog box, type *ns*

   

8. Click OK

   

9. Right-click any cell below State and click Clear Filter From State

Filtering by an Un-Equal Sub-String

To select records that have a sub-string different than the one you indicate, select a character or a sub-string in the desired column. Right-click the selected leter or sub-string. Position the mouse on Text Filters and click Does Not Equal... In the dialog box, you can type the exact or an approximate string to look for:

• If you type an exact string, this would be the same as the Does Not Equal option. After typing the string and clicking OK, Microsoft Access would look for the records that have the exact string. All entries that have the sub-string would be excluded from the result. For example if you type Marshall, all records that do not have Marshall would be in the result
• If you do not know the beginning of a string, you can precede a character or a sub-string with the * symbol. For example, if you type *ar*, all entries that include the sub-string ar would be excluded from the list

Filtering by a Beginning Sub-String

To select records that start with a certain letter or a sub-string of your choice, select the character or sub-string in the desired column. Right-click the selected leter or sub-string, position the mouse on Text Filters and click Begins With... In the dialog box, if you type just one character, such as c, you would get all entries that start with that character.

Practical Learning: Filtering by a Beginning Sub-String

1. In the Region column, double-click the word East to select it
2. In the Sort & Filter section of the Ribbon, click Selection and click Begins With "East"

   

   

3. In the Sort & Filter section of the Ribbon, click the Toggle Filter button .
4. Right-click anything below State, position the mouse on Text Fields, and click Begins With...
5. To see only the states whose names start with m, in the Customer Filter dialog box, type m and click OK

   

6. In the Sort & Filter section of the Ribbon, click the Toggle Filter button

Filtering by a Sub-String that Does Not Begin

You can right-click a cell or text box, position the mouse on Text Filters, and click Does No Begin With.... In the dialog box, you can type one or a few characters that the values in the column should not start with. This would produce the opposite results of the Begins With option.

Filtering by a Contained Sub-String

To get a list of records that contain a certain letter or sub-stirng, right-click a cell or text box in the desired column, position the mouse on Text Filters, and click Contains... In the dialog box, you can type a string or an approximation to look for:

• If you use an exact string, only the records that exactly include that string would be in the resulting list
• If you don't know the exact string but know what sub-string it contains, you can use the asterisk to provide it. For example, you can user *ar*

You can also provide a list of OR characters that the string should contain. That is, you can ask Microsoft Access to find strings that contain this character, or that character, or that character, and so on. To do this, use [] and, in [], type the characters. An example would be [dgu]

This would produce all strings that include d, all strings that include g, and all strings that include u. Examples would be Arthur, Raymond, Bouba, Gabrielle, Maurice, or Orlando.

Practical Learning: Filtering by a Contained Sub-String

1. Right-click any cell below State, position the mouse on Text Filters, and click Contains...
2. In the dialog box, type *is* and press Enter

   

3. In the Sort & Filter section of the Ribbon, click the Toggle Filter button

Filtering by a Sub-String that is not Contained

When filtering, you can choose to avoid records that don't contain a certain letter or sub-string. In this case, you would select Does Not Contain... when you right-click a cell or text box and position the mouse on Text Filters... In the dialog box, you can type a sub-string to look for. Any string that includes that sub-string would be excluded from the list. You can also use the square brackets to specify the characters that should not be found in some strings. For example, you can use [dgu]

In this case, the result would include all strings that don't have any of the characters in the square brackets.

Filtering by an Ending Sub-String

If you want to filter for records that end with a certain letter or string, after right-clicking a cell or text box, position the mouse on Text Filters, and click Ends With... In the dialog, you can type one or a few characters that the content of the values in the column should end with:

• You can type just one character, such as e. You would get all entries that end with that character, such as Jeannette, Pete, Catherine, or Maurice
• You can also provide an approximation of the characters or sub-strings to end with. For example, you can ask Microsoft Access to find all records where the string ends with any letter from a to e for the alphabet. In the this case, in the dialog box, you can type [ae]

Practical Learning: Filtering by an Ending Sub-String

1. Right-click any cell below State, position the mouse on Text Filters, and click Ends With...
2. To get a list of states whose names end with a, in the dialog box, type a and click OK
3. On the Ribbon, click File and click Open
4. In the list of files, click Chemistry2
5. When asked whether you want to save the table, click No
6. In the Navigation Pane, double-click the Periodic form
7. On the form, below the Name column, select en at the end of Hydrogen
8. Right-click the selection and click Ends With en

   

   

9. In the Sort & Filter section of the Ribbon, click the Toggle Filter button
10. Right-click any text box below Name, position the mouse on Text Filters, and click Ends With...
11. In the dialog box, type ium

    

12. Click OK

    

13. Close the form

Filtering by a Different Sub-String

To select records that don't end with a certain letter or a sub-string, right-click a cell or text box, position the mouse on Text Filters, and click Does No End With...

Practical Learning: Ending the Lesson

• Close Microsoft Access