Microsoft Office 2000

Lesson 7: Access 2000 (Part 2)

Using Filters and Queries

Using Filters
When you have a large database you will likely need to use filters and queries to retrieve information.

You can filter records in the following way:

• Filter By Selection - use if you can easily find and select in the form or datasheet an instance of the value you want the filtered records to contain
• Filter By Form - use if you want to choose the values you're searching for from a list without scrolling through all the records in a datasheet or form, or if you want to specify multiple criteria at once
• Filter For Input - use if the focus is in a field in a form or datasheet and you just want to type in place the exact value you're searching for or the expression whose result you want to use as your criteria

To filter records by selection:

• In a field on a form, subform, datasheet, or subdatasheet, find one instance of the value you want records to contain in order to be included in the filter's results
• Select the value, and then click Filter By Selection on the toolbar
• Repeat with other values until you have the set of records you want

To filter records by form (entering values in a blank view of your form or datasheet):

• Open a table, query, or form in Datasheet view or open a form in Form view
• Click Filter By Form on the toolbar to switch to the Filter By Form window
• Click the field in which you want to specify the criteria that records must meet to be included in the filtered set of records
• Enter your criteria by selecting the value you are searching for from the list in the field (if the list includes field values), or by typing the value into the field

To filter records by input (entering criteria in place in a form or datasheet):

• Open a table, query, or form in Datasheet view, or a form in Form view.
• To specify the exact field value you want the filtered records to contain, right-click in the field in the datasheet you're filtering, and type the value in the Filter For box on the shortcut menu.
• To specify more complex criteria, type the complete expression using the appropriate combination of identifiers, operators, wildcard characters, and values to produce the result you want
• To apply the filter and close the shortcut menu, press Enter
• To apply the filter and keep the shortcut menu displayed (so that you can specify additional criteria for the field), press Tab. Enter new criteria, and then press Tab again. Repeat until you have only the records you want.

Using Queries
You can use queries to view, change, and analyze data in a variety of ways. You can also use them as the source of records for forms, reports, and data access pages.

The most common type of query is a select query. A select query retrieves data from one or more tables using criteria you specify. It then displays it in the order you want.

To create queries from filters you created using Filter By Form, Filter By Selection, or Filter For Input, simply save the filter as a query.

You can create a query with a Wizard or from scratch in query Design view. In Design view, you simply select the data you want to use by adding tables or queries that contain the data, and then fill in the design grid.

Instead of creating a query from scratch, you can also let Access create a query for you by using the Wizard.

To create a query to use as the basis of a form, report, or data access page:

• Use the form, report, or data access page wizards

To create a query that you want to run independently or base multiple forms, reports, and data access pages on:

• Use one of the query wizards

Query wizards do all the basic work for you after you provide answers to a series of questions.

For additional information on queries see Using Queries in Microsoft Office 2000 for Windows: Visual QuickStart Guide pp. 347-360.

Well done! You have now learned elements of using Access including creating forms, creating reports, and using filters and queries.

Lesson 7 Bibliography:

• Creating a Form in Microsoft Office 2000 for Windows: Visual QuickStart Guide pp. 323-339.
• Creating a Report in Microsoft Office 2000 for Windows: Visual QuickStart Guide pp. 361-372
• Using Queries in Microsoft Office 2000 for Windows: Visual QuickStart Guide pp. 347-360

Lesson 7 Discussion: Try out several Microsoft Access wizards and share your favorite. Tell which wizard it is and how you found it useful.

Print this page

  1  2  3  4