🔬Microsoft Access Filter by Selection and Form

Master Data Filtering in Microsoft Access Study Guide

What is filter by selection in Access?

Filter by selection is a quick method in Access to filter data based on the currently selected value in a table field. You click on a specific data point within a field (like "Condiments" in the category field) and then use the "Selection" option under the "Sort & Filter" section of the "Home" tab. This allows you to display only the records that "equal," "do not equal," "contain," or "do not contain" the selected value.

How do I filter for records that are not equal to a selected value using filter by selection?

To filter for records that do not match a specific value, first click on a cell containing the value you want to exclude. Then, go to the "Home" tab, find the "Sort & Filter" group, click "Selection," and choose the "Does Not Equal [Selected Value]" option from the dropdown menu. This will display all records except those where the chosen field contains the selected value.

What is filter by form in Access?

Filter by form is a more advanced filtering method in Access that provides a blank form-like interface representing your table fields. You enter criteria into the relevant fields on this form to filter your data. This method allows for more complex filtering conditions than filter by selection and is accessed through the "Advanced" option within the "Sort & Filter" group on the "Home" tab, after selecting "Filter by Selection."

How do I create an "OR" condition using filter by form?

To create an "OR" condition in filter by form, you enter your first filtering criteria in the initial "Look For" tab. After entering criteria in a field, an "Or" tab will appear at the bottom of the window. Clicking on this "Or" tab will open a new blank form where you can enter a different set of criteria. Each additional "Or" tab you use represents another alternative condition for filtering your data (e.g., "Canned Meat" OR "Dairy Products").

How do I create an "AND" condition using filter by form?

In filter by form, an "AND" condition is created by entering multiple criteria within the same "Look For" or "Or" tab. Any criteria entered on the same tab and on the same line are treated as an "AND" condition, meaning that a record must meet all the criteria on that specific tab to be included in the filtered results (e.g., "Canned Meat" AND "Target Level > 60"). There is no dedicated "And" tab; it is implicit when entering multiple criteria on a single line within a tab.

Can I combine "AND" and "OR" conditions in filter by form?

Yes, you can combine "AND" and "OR" conditions in filter by form. You achieve this by using the "Or" tabs to create different sets of conditions (the "OR" part). Within each of these tabs, you can enter multiple criteria on the same line to create "AND" conditions. For example, you could filter for records that are ("Canned Meat" AND "Target Level > 60") OR ("Dairy Products").

How do I clear filters in Access?

To clear all applied filters in Access, go to the "Home" tab, find the "Sort & Filter" group, click on the "Advanced" dropdown menu, and select "Clear All Filters." It is recommended to clear all filters before starting a new filter by form operation to ensure you are working with the complete dataset.

What is the difference between filter by selection and filter by form?

Filter by selection is a quick and simple method for filtering based on the exact value of a single field in your table, offering options like "equals" or "does not equal." Filter by form is a more advanced method that uses a form-like interface to enter multiple criteria across different fields, allowing for complex "AND" and "OR" conditions and criteria beyond just exact matches (like using comparison operators like "greater than").

Quiz

1. What is the main advantage of using "Filter by Selection" compared to using the large filter button in Access?
   

2. How do you activate the "Filter by Selection" feature in Access?
   

3. Explain the four options available when using "Filter by Selection" on a text field.
   

4. Before using "Filter by Form," what crucial step should you take to ensure you start with a clean slate?
   

5. In "Filter by Form," where do you select the field you want to filter on?
   

6. How do you add an "OR" condition when using "Filter by Form"?
   

7. In "Filter by Form," where are the criteria for an "AND" condition placed?
   

8. If you set a criterion in the "Canned Meat" tab of "Filter by Form" and a criterion in the "Dairy Products" tab, is that an "AND" or an "OR" condition?
   

9. If you set a criterion for "Category" and "Target Level" on the same line within the "Canned Meat" tab of "Filter by Form," is that an "AND" or an "OR" condition?
   

10. After applying a filter using either "Filter by Selection" or "Filter by Form," what is the best practice to return to viewing all records?

Quiz Answer Key

1. "Filter by Selection" is a quicker way to find specific data based on the value you are currently clicked on, making it faster for simple filters.
   

2. To activate "Filter by Selection," click on the data you want to filter by, go to the "Home" tab, and then select "Selection" in the "Sort & Filter" group.
   

3. The four options are "Equals," "Does Not Equal," "Contains," and "Does Not Contain."
   

4. Before using "Filter by Form," you should clear all existing filters by going to "Advanced" and selecting "Clear All Filters."
   

5. In "Filter by Form," you select the field by clicking in the row directly underneath the column heading for that field.
   

6. You add an "OR" condition by clicking on the "or" tab that becomes enabled at the bottom left after you have set an initial criterion.
   

7. The criteria for an "AND" condition are placed on the same line within the "Filter by Form" window.
   

8. Setting criteria in different tabs ("Canned Meat" and "Dairy Products") in "Filter by Form" creates an "OR" condition.
   

9. Setting criteria for "Category" and "Target Level" on the same line within the "Canned Meat" tab of "Filter by Form" creates an "AND" condition.
   

10. To return to viewing all records, you should toggle the filter off or go to "Advanced" and select "Clear All Filters."

Glossary of Key Terms

• Filter by Selection: A quick method in Access to display records that match or do not match the selected value in a field.
  

• Filter by Form: An advanced filtering method in Access that allows you to specify criteria in a form-like interface, enabling the use of "AND" and "OR" conditions across multiple fields and values.
  

• Toggle Filter: A button or action that switches between applying and removing the current filter on a table or form.
  

• Clear All Filters: An action that removes all active filters from a table or form, displaying all records.
  

• Selection (in Filter by Selection): The menu that appears after clicking on data and choosing "Selection," offering options like "Equals," "Does Not Equal," "Contains," and "Does Not Contain."
  

• Advanced (in Filter by Selection/Form): A menu option that leads to more complex filtering options, including "Filter by Form."
  

• Look For (in Filter by Form): The section at the bottom left of the "Filter by Form" window where you can view the currently applied criteria.
  

• or tab (in Filter by Form): A tab that appears at the bottom left of the "Filter by Form" window after setting a criterion, allowing you to add additional criteria that create an "OR" condition with previous criteria.
  

• AND condition: A filtering condition where all specified criteria must be met for a record to be displayed. In "Filter by Form," these criteria are on the same line.
  

• OR condition: A filtering condition where at least one of the specified criteria must be met for a record to be displayed. In "Filter by Form," these criteria are on different "or" tabs.
  

• Criterion (plural: criteria): A condition or rule used to filter data, such as "Category equals 'Condiments'" or "Target Level is greater than 60."
  

• Field: A column in an Access table that represents a specific piece of information, such as "Category" or "Target Level."
  

• Record: A row in an Access table that represents a single entry or item, containing data across multiple fields.
  

• Table: A fundamental object in Access that stores data in a structured format of rows (records) and columns (fields).