Filter Records using Multiple Criteria

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.

Filters allow you to view only the data you want to see. When you create a filter, you set conditions 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 condition, and temporarily hides the ones that do not meet your criteria.
Filters are useful because they allow you to focus in on specific records without being distracted by the data you are not interested 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.

While Filter by Selection is simple to use it may not meet your needs. You may have criteria for more than one field, or you may have more than one criterion for one field. Filter by Selection does not provide the flexibility for multiple criteria, but Filter by Form offers you more options if you need to filter using more than one field in a datasheet. Use this Simulation to learn how to use Filter by Form.

1. As with Filter by Selection the first step to filtering by form is to display the table you want to filter. In this simulation you will be finding clients with addresses in Texas and Colorado. Display the Filter by Form window by choosing

   Records>>Filter>>Filter by Form
   

   from the menu.
2. This is the Filter by Form window. It contains a cell for each field, and two tabs at the bottom of the window: Look for and Or. Using this form you can define multiple criteria. The first criterion that you will select is to find addresses in Texas. Click the down arrow in the State field and choose TX.
3. Each field has a drop-down list containing all the values stored in that field. You saw the selections in the State field,equivalent selections are available for all the fields. If you select a value in another field, as well as the State field, you are telling Access to find records that meet both of those criteria. However, you want to find addresses in either California or Texas. To specify an Or criteria, you need to use the Or tab at the bottom of the window.
   Click the Or tab to see another copy of the form.
4. When you click the Or tab you see a new copy of the Filter by Form window. Use this form to specify Or criteria.
   Select CA from the State drop-down list.
5. Now you have defined your filter,addresses that are in either Texas or California,and you are ready to see the results. To filter the table using the criteria you have defined in the Filter by Form window, click the Filter button on the toolbar ,it looks like a funnel.
6. The result is three records. The bottom line of the window tells you the table is filtered, just like it does when you filter y selection. If you want to see the entire table, click the Remove Filter button.

Remember that criteria selected on the same tab must both be met in order for the record to “pass” the filter. When criteria are selected on different tabs, only the criteria on one tab must be met for the record to be displayed.
Click the link below to learn more about how to filter records using multiple criteria.
FilterRecords using Multiple Criteria