Finding, Filtering, Sorting  «Prev  Next»
Lesson 7

Single Table Access Conclusion

This module discussed how to find the data you need when it resides in a single table.

Terms and Concepts

This module introduced you to the following key terms and concepts:
  1. Filter
  2. Filter by Form:
  3. Filter by Selection
  4. Filter For
  5. Find: Search for text or values.
  6. Sort: The sorting process involves putting records in order. Usually you will sort using one field, and you may sort in ascending or descending order.

Difference between 1) Filter by Form and 2) Filter by Selection in Microsoft Access

In Microsoft Access, "Filter by Form" and "Filter by Selection" are two different methods used to filter data in tables, forms, or queries. Each method serves a unique purpose and is suitable for different situations.
  1. Filter by Form:
    • How it Works: When you use Filter by Form, Access provides a blank form based on your data. You can then enter the criteria you want to filter by in the respective fields. This method allows you to specify complex filtering criteria across multiple fields. For example, you might want to see records where the "Status" is "Open" and the "City" is either "New York" or "San Francisco."
    • Use Case: Filter by Form is particularly useful when you need to apply multiple or complex filters to a dataset. It gives you a clear and structured way to specify various filtering conditions, including OR conditions within the same field.
    • Flexibility: Offers the ability to apply filters on multiple fields simultaneously and to specify different types of criteria, including using wildcards or comparison operators.
  2. Filter by Selection:
    • How it Works: Filter by Selection allows you to quickly filter records based on a value you select in a field. For instance, if you're viewing a table of customer information and you click on a cell containing the city "Boston," you can use Filter by Selection to instantly view all records where the city is "Boston."
    • Use Case: This method is handy for quickly narrowing down data based on a specific value you're interested in. It's particularly useful for ad-hoc filtering when you want to see all records that match a particular value you come across.
    • Flexibility: While very convenient for fast filtering, Filter by Selection is more limited compared to Filter by Form. It generally applies only to the specific value and field you've selected, without the ability to specify complex criteria or include multiple fields in a single filter action.

  • Filter by Form** is more versatile and suited for complex filtering needs, allowing for detailed criteria specification across multiple fields.
  • Filter by Selection** offers a quick and straightforward way to filter data based on a specific value in a field but is less flexible for more complex filtering requirements.

Choosing between these two methods depends on your specific needs: whether you're performing a quick filter based on a single value or you require a more complex, multi-criteria filter.

You can enter a specific value or choose to use wildcard characters. Table 2.7 lists the wildcard characters available in the Find dialog box.
Table 2.7 Wildcard characters
Table 2.7 Wildcard characters

You can combine wildcard characters for more robust searches. For example, 196[!2.8] will find 1961 and 1969, but nothing in between. The Match drop-down list contains three choices:
  1. Any Part of Field: If you select Any Part of Field, Access searches to see whether the value is contained anywhere in the field. This search finds Ford anywhere in the field, including values like Ford Mustang, 2008 Ford F-150, and Ford Galaxy 500.
  2. Whole Field: The default is Whole Field, which finds fields containing exactly what you have entered. For example, the Whole Field option finds Ford only if the value in the field being searched is exactly Ford, and nothing else.
  3. Start of Field: A search for Ford using the Start of Field option searches from the beginning of the field and returns all the rows containing Ford as the first four characters of the description.

Apply Filters Sorts to Database - Exercise

Put your data to the test by filtering the Consulting database.
Apply Filters Sorts to Database - Exercise

SEMrush Software