Home

Special Windows for Data Selection and Record Filtering

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:

  • Click a column header or any cell under a column for a table or query, or click a control or its accompanying label on a form. Then, in the Sort & Filter section of the Ribbon, click the Filter button Filter
  • On a table or query, click the down-pointing button on the right side of the caption of the column

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:

Filtering Records

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):

Filtering Records - Filtering By Value

Then click the check box on the one item you want. Here is an example:

Filtering Records

Then click OK. This would display only the records that share the value that had the check box:

Filtering

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 Toggle Filter on the Ribbon. An alternative is to click the Filtered button Filtered 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.

Practical Learning: Filtering for a Value

  1. Start Microsoft Access and open the Altair Realtors2 database from the previous lesson
  2. In the Navigation Pane, double-click the Properties form to open it
  3. On the form, click Condition
  4. If necessary, on the Ribbon, click Home.
    In the Sort & Filter section of the Ribbon, click Filter Filter
  5. In the list that appears, click (Select All) to clear all check boxes
  6. Click the Excellent check box

    Filtering in a Form

  7. Click OK
  8. Navigate to various properties and notice that only those in excellent condition display

    Filtering Using a Form

    Filtering Using a Form

  9. On the Ribbon, click Toggle Filter Toggle Filter

Filtering By Form

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:

Filter by Form

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:

Filter by Form

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:

  • Right-click the column header on the table or query, or the control (or its accompanying label) on the form, and click Apply Filter/Sort
  • In the Sort & Filter section of the Ribbon, click the Toggle Filter button
  • In the Sort & Filter section of the Ribbon, click Advanced and click Apply Filter/Sort

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:

  • Right-click a cell of the column on which the filtering was performed on a table or query, or right-click the control (or its accompanying label) on the form, and click Clear Filter From ... (this is followed by the name of the field or control)
  • In the Sort & Filter section of the Ribbon, click the Toggle Filter button Toggle Filter
  • Click any cell in the table or query, or click any control on the form. In the Sort & Filter section of the Ribbon, click Advanced and click Clear All Filters

Practical Learning: Filtering By Form

  1. If necessary, on the Ribbon, click Home.
    On the Ribbon, click Advanced and click Filter By Form
  2. Right-click anywhere on the form and click Clear Grid
  3. Click Stories, then click the arrow of the appearing combo box and select 3

    Filter by Form

    Filter by Form

  4. Right-click an unoccupied area of the form and click Apply Filter/Sort

    Filter by Form

  5. Navigate to different records. Notice that only the properties that have 3 levels are displayed

    Filter by Form

    Filter by Form

  6. Close the form
  7. Open the StatesStatistics3 database from the previous lesson
  8. On the Ribbon, click Create and click Query Design
  9. In the Show Table, click States, click Add, and click Close
  10. In the top list, double-click StateName, AreaSqrMiles, AdmissionUnionDate, Capital, and Region
  11. Preview the results in the Datasheet View
  12. On the Ribbon, click Advanced and click Filter By Form
  13. Click the cell below Region
  14. Click the arrow of its combo box and select New England
  15. To execute, on the Ribbon, click the Toggle Filter button Toggle Filter
  16. In the Home tab of the Ribbon, change the following characteristics:
    Change the following characteristics:
    Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
    Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
    Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
    Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)

    Filtering by Form

  17. To change the filter, on the Ribbon, click the Toggle Filter button Toggle Filter
  18. On the Ribbon, click Advanced and click Filte By Form
  19. Click the cell below Region, press Home, and type <> (to get <>"New England")
  20. To execute, right-click anywhere in the window and click Apply Filter/Sort

    Filtering by Form

  21. To remove the filter, click the button on the right side of Region on the window and click Clear filter from Region

    Filtering By Form

  22. On the Ribbon, click Advanced and click Filte By Form
  23. Delete <>"New England"
  24. Click the cell below Area in Sqr Miles and type <= 20000 and press Enter
  25. To apply, on the Ribbon, click Advanced and click Apply Filter/Sort

    Filtering By Form

  26. On the Ribbon, click Advanced and click Filte By Form
  27. Delete the <= 20000 expression
  28. Click the cell below Date of Admission to Union and type BETWEEN #1/1/1880# AND #12/31/1920# and press Enter
  29. Apply the filter using one of the techniques we used already

    Filtering By Form

  30. On the Ribbon, click Advanced and click Filter By Form
  31. Delete the expression on the window
  32. Click the cell below Region and type IN("mountain", "pacific", "new england") and press Enter
  33. Apply the filter

    Filtering By Form

  34. Close the query without saving it
 
 
 

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:

  • Right-click the window and click Apply Filter/Sort
  • In the Sort & Filter section of the Ribbon, click Advanced, and click Apply Filter/Sort

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.

Practical Learning: Filtering Using the Object Filter Window

  1. Open the Monson University1 database from Lesson 24
  2. In the Navigation Pane, double-click the Employees table
  3. Change the following characteristics:
    Font Name: Constantia (if you don't have that font, select Times New Roman)
    Font Color: White
    Background Color: More Colors: Red: 128, Green: 0, Blue: 0
    Alternate Row Color: Maroon (Standard Colors: 6th column, 1st row)

    Filtering Using the Object Filter Window

  4. On the Ribbon, click Advanced and click Advanced Filter/Sort...
  5. From the top section, double-click DepartmentCode
  6. In the bottom section, click the Criteria box for the DepartmentCode column and type fina
  7. Right-click an unoccupied area of the window and click Apply Filter/Sort

    Filtering Using the Object Filter Window

    Filtering Using the Object Filter Window

  8. On the Ribbon, click Advanced and click Advanced Filter/Sort...
  9. In the bottom side of the window, replace DepartmentCode with Title
  10. Replace fina with Like '*dean*' (meaning we want all records where the title insludes the word Dean)
  11. On the Ribbon, click Advanced and click Apply Filter/Sort...

    Filtering Using the Object Filter Window

  12. Close the table without saving it

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 Ellipsis 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:

The Design Tab of the Ribbon for the Quiry Buildder

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 Datasheet View or the Run 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

  1. On the Ribbon, click Create and click Form Design
  2. In the Property Sheet, click the All tab, then click Record Source and click its ellipsis button Ellipsis
  3. In the Show Table dialog box, click Students, click Add, and click Close
  4. In the top list, double-click StudentNumber, FirstName, LastName, Gender, and MajorID
  5. In the bottom side of the window, click the Criteria box for MajorID and type 1021

    Form Design - The Query Builder

  6. On the Ribbon, click the Close button Ellipsis
  7. When asked whether you want to save the changes, click Yes
  8. Save the form as Computer Science Students
  9. On the Ribbon, click Design and click Add Existing fields
  10. In the Field List, double-click StudentNumber, FirstName, LastName, and Gender
  11. In the Controls section of the Ribbon, click the Combo box and click the form
  12. In the first page of the wizard, make sure the first radio is selected and click Next
  13. In the second page of the wizard, click Table: Majors and click Next
  14. In the 3rd page of the wizard, double-click Major and click Next
  15. In the 4th page of the wizard, click Next
  16. In the 5th page of the wizard, click Next
  17. In the 6th page of the wizard, click the arrow of the combo box and select MajorID
  18. Click Next and click Finish
  19. Design the form as follows:

    Form Design - The Query Builder

  20. Save a nd close the form
  21. Close Microsoft Access
 
 
   
 

Previous Copyright © 2008-2016, FunctionX, Inc. Next