Special Windows for Data Selection and Record Filtering
Filtering By Value
In a typical table, each column has one or more values. As mentioned in our introduction to sorting, a column may have the same value(s) occurring over and over again while another column may have a different value for each record. To assist you in selecting a value that repeats in a field, Microsoft Access provides a convenient window with the list of values where each is unique. To display that list:
In both cases, a window would display, containing the values of the column on which the action was performed. Each value has a check box:
In the window that comes up, besides the values of the column, there are the (Select All) and the (Blanks) items (the values of the columns in the checked list box are arranged in alphabetical order; Select All and Blanks are in parentheses because they are not considered for the alphabetical arrangement). To dismiss the window, press Esc or click Cancel.
When the window comes up, to select only the one value whose record(s) must be displayed, clear the check box of (Select All):
Then click the check box on the one item you want. Here is an example:
Then click OK. This would display only the records that share the value that had the check box:
In the same way, you can show only empty fields by selecting the (Blanks) option.
To remove the filter, we saw that you could click the Toggle Filter button on the Ribbon. An alternative is to click the Filtered button in the bottom side of a table, a query, or a form. As an alternative, display the window again, click the (Select All) option and click OK.
Besides the techniques and windows we have used so far for data analysis, Microsoft Access provides another feature referred to as filtering by form. To start it, open a table or query in Datasheet View, or a form in Form View or in Layout View. On the Ribbon, click Home. In the Sort & Filter section of the Ribbon, click Advanced and click Filter By Form. The table, query, or form would become empty and all records would get hidden:
To select a value, click the cell under the column header. A combo box would come up. Click the arrow of that combo box to display its values:
The combo box includes each distinct value of the column. If some records are empty, there would be a first empty blank field in the list. To filter the records, select the blank or the desired value from the list. As an alternative, the combo box is an editable text box. This means that, instead of selecting a value from the list, you can type a Boolean expression.
After making a selection or typing the expression, to apply the filter:
This action would cause the table, query, or form to display the results.
After filtering, the value (criterion) is stored in memory and you can filter again as many times as you want. Otherwise, you can remove the filter. To do this:
Practical Learning: Filtering By Form
The Object Filter Window
To assist you in setting a condition for data analysis, Microsoft Access provides a window that resembles the Design View of a query. The window allows you to select one or more fields and set a (some) condition(s) on it/them. The name of that window is a combination of the name of the object from which you opened it, the word Filter, and an incremental number (1, 2, 3, and so on).
To open the Object Filter window, display the table, query, or form in Datasheet View, the form in Form View or in Layout View, or the report in Report View or in Layout View. On the Ribbon, click Home. In the Sort & Filter section of the Ribbon, click Advanced and click Advanced Filter/Sort... A window like the Design View of a query would display.
To use the Object Filter, select (only) the column(s) on which you want to apply a condition. After selecting a column, in the bottom section of the window, in the Criteria box corresponding to the column, type a Boolean expression. After selecting a column and setting a condition, to apply it:
To remove the previous criterion, right-click the window and click Clear Grid. After using the grid, you can close it and keep the table, query, or form open.
The Query Builder
Most forms and reports use a table or an existing query as their Record Source. As an alternative, you can specify a SQL statement as the Record Source of a form or report. If you know the statement you want to use, you can directly type it in the Record Source field of the Property Sheet of a form or report in the Design View. As an alternative, you can click the ellipsis button of the Record Source. This would open a special window that resembles the Design View of a query. It is called the Query Builder.
When the Query Builder displays, the Ribbon has a Design tab:
As seen when designing a query, after selecting some fields and optionally setting a condition, if you want to preview the result, click either the View button or the Run button. When you have finished building the query, to return to the form or report, click the Close button on the Ribbon. You may receive a message. Read it and click Yes.
After creating the form or report, if you delete it (the form or the report), the SQL statement would be lost also.
Practical Learning: Using the Query Builder