Refining Queries  «Prev  Next»

Filtering by Form and Selection in Access

For further practice, try this on your own in Microsoft Access, using another instance.
  1. To start with, use the query called qryClientsAndProjects in the project database.
    Click the View button to work with the results of this query.
  2. You now have the results set, with the client named "Network Consultants, Inc." highlighted. Click the Filter by Selection toolbar button.
  3. Now you can see the four records filtered from the rest of the query’s result set. Remove the filter by clicking the Remove Filter toolbar button.
  4. Here are all the records once again. Now it’s time to work with the Filter by Form feature. Click the Filter by Form toolbar button.
  5. You can see the filter you just set with the Filter by Selection feature. At this point, choose a different company for which to filter. Click the drop-down for the Company field.
  6. Now choose the company called "ABC Webworks".
  7. The company chosen will be displayed in the Company drop-down. Now it’s time to apply the filter. Click the Apply Filter button.
  8. There you go: the new results with the applied filter.


Filtering by form

Filter by Form lets you enter criteria into a single row on the datasheet. Clicking the Filter by Form button transforms the datasheet into a single row containing a drop-down list in every column. The drop-down list contains all the unique values for the column. An Or tab at the bottom of the window lets you specify OR conditions for each group. Choose 
Advanced => Filter by Form
in the Sort & Filter group of the Ribbon to enter Filter by Form mode, shown in Figure 3.3.

Using Filter by Form lets you set multiple conditions for fi ltering at one time. Notice the Or tab at the bottom of the window.
Figure 3.3 Filter by Form
Figure 3.3 Filter by Form

Select values from the combo boxes or type values you want to search for in the field. If you want to see records where the Category is Trucks or SUVs, select Trucks from the Category drop-down list, select the Or tab at the bottom of the window, and then select SUVs from the Category drop-down list. To see records where Category is SUV and QtyInStock is 1, select SUV from the Category drop-down and type 1 in QtyInStock. Once you enter the desired criteria, click the Toggle Filter command to apply the filter. Enter as many conditions as you need using the Or tab. If you need even more advanced manipulation of your selections, you can choose Advanced . Advanced Filter/Sort from the Sort & Filter group of the Ribbon to get an actual Query by Example (QBE) screen that you can use to enter more-complex criteria.