Filtering records in a table is the process of displaying only a portion of the total recordsa subsetbased on matching specific values. Filters are commonly used to provide a quick answer, and the result is not generally saved for future use.
James Smith would like to see only the records for the Radio, TV, & Film Club, a club that he personally sponsors. To display the records he wants to see, use the Filter By Selection command, which retrieves only the records that contain the value in the selected field. The records in the table that do not contain the value in the selected field are removed from view.
In the Club Affiliation field, click in any record that contains Radio, TV, & Film Club. On the Table Datasheet toolbar, click the Filter by Selection button , and then compare your screen with Figure 2.4.
Three records display that match the value Radio, TV, & Film Club. At the bottom of the window, in the navigation area, (Filtered) displays next to the number of records, and on the status bar, FLTR displays to indicate that a filter has been applied. On the Table Datasheet toolbar, the Remove Filter button is active, indicated by an orange background.
NoteApply/Remove Filter Button
On the Table Datasheet toolbar, the Apply/Remove Filter button is used to apply or remove a filter. If no filter has been created for the table the button is not activeit is dimmed. After a filter is created, this button becomes active. Because it is a toggle button used to apply or remove filters, the ScreenTip that displays for this button will alternate between Apply Filter, when a filter has been created but is not currently applied, and Remove Filter, when a filter has been applied to the table.
On the Table Datasheet toolbar, click the Remove Filter button to display all of the records, and notice that the Apply/Remove filter button is still active.
Click the Apply Filter button to reapply the filter and display the three records that match Radio, TV, & Film Club.
Click the Remove Filter button to redisplay all of the records. In the Pledge Amount field, locate any record with $25 and click in the field. Then on the Table Datasheet toolbar, click the Filter by Selection button .
On the Table Datasheet toolbar, click the Remove Filter button to redisplay all of the records.
Use the Filter By Form command to filter the records in a table based on one or more fields, or based on more than one value in the same field. The Filter By Form command offers greater flexibility than the Filter by Selection command when you want an answer to a question that requires matching values in more than one field. For example, James Smith wants to know how many pledges were obtained by student #20201 on behalf of the Entrepreneurs of LMCC club.
On the Table Datasheet toolbar, click the Filter By Form button and compare your screen with Figure 2.5.
On the Filter/Sort toolbar, click the Clear Grid button to remove 25 from the Pledge Amount field.
Under Student#, click in the empty field box to display its list arrow, and then click the arrow. From the displayed list, click 20201. Alternatively, you can type the value if you know it, but selecting from the list is often faster. On the Filter/Sort toolbar, click the Apply Filter button .
On the Table Datasheet toolbar, click the Filter By Form button to return to the Filter by Form window.
Click in the Club Affiliation field box to display its list arrow, click the arrow, and then from the displayed list, click Entrepreneurs of LMCC.
On the Filter/Sort toolbar, click the Apply Filter button .
On the Table Datasheet toolbar, click the Filter By Form button to display the Filter by Form window, and then click the Clear Grid button .
Click in the Club Affiliation field box, click the arrow, and then click Entrepreneurs of LMCC. In the lower left corner of the Filter by Form window, click the Or tab to display a new filter form. Under Club Affiliation, click the arrow, and then click International Business Student Association.
Click the Apply Filter button , and then compare your screen with Figure 2.6.
In the Club Affiliation field, a total of seven records contain either the value Entrepreneurs of LMCC or the value International Business Student Association.
Click the Filter By Form button , and then click Clear Grid to remove all of the values from the filter form.
Click the Apply Filter button .
Close the table window, and when prompted to save changes, click No.
Access recognizes that you made changes to the table, even though you cleared the grid and re-sorted the data on its primary key. Therefore, Access prompts you to save the changes. Click No to return the data to its original conditionsorted by the primary key, and no filters.
NoteRemoving Filters from a Table
After a filter is created, it becomes part of the table until it is replaced with another filter. If the Apply Filter button is active, it is a visual indication that a filter has been created and is still an active part of the table. To ensure that the filter does not become a permanent part of the table, do not save the table when you close it. If a filter is saved with a table, it might be applied without the individual who is using the database being aware that the data displayed is a subset of the entire table. For this reason, use filters to answer quick questions. If a filter is useful and needs to be used again, create it as a query so that it can be saved and applied to the data when necessary.
GO! with Microsoft Office 2003 Brief (2nd Edition)