The University of British Columbia

What is a Filter?

A filter allows application of a set of selection criteria and/or sorting instructions to the records in a table. It is a quick and temporary tool that is created for one-time use in the context of a particular table.

How to Create a Filter?

To create a filter, first open the table you want to work on. Then click the Edit Filter/Sort button in the toolbar. Finally enter selection criteria and/or sorting instructions in the filter window.

Exercise: Your boss asks you to find all the products with unit price greater than $40, sorted in descending order of unit price.

1. From the Database window, click Table and open the Products table by double clicking Products in the list.

   

2. Click the Edit Filter/Sort button in the toolbar. In response, Access opens a filter window named ProductsFilter1.

   
3. Select Unit Price in the field list, and drag it down to the first column of the filter grid.
4. Select the Sort row in the Unit Price column, and click the down-arrow button to view the list of sorting options; select Descending.

5. Select the Criteria row in the Unit Price column and enter >40 .

   

6. Click the Apply Filter/Sort button in the toolbar. In response, Access selects all the records in the Product table that meet the criteria and lists them in descending order.

   
7. To print the filtered records, choose Print command from the File menu.

What is a Query?

In general, there are two types of query: Select and Action. A select query gathers, collates and presents information in usable forms. An action query makes changes in specified records of an existing table, or creates a new table. In this course, we will concentrate on select queries only.

Sometimes, information is scattered in many tables. For example, customers' information is stored in Customers table while their ordering information is stored in Orders table. In order to retrieve information from more than one table, queries are used. Moreover, queries are capable of performing the functions of filters, namely adding selection criteria and sorting instructions. In addition, queries allows calculation of new fields.

In contrast to a filter, a query is reusable. When a table is closed, the selection criteria and/or sorting instructions will be gone. Therefore, in order to retrieve the same records again, the filter has to be recreated. However, a query allows the selection criteria and/or sorting instructions to be saved and reused.

How to Create a Query?

Exercise: Your boss asks you to find all the products under the category Beverages and the total costs for each of these products. In addition, you have to find the suppliers' information of these products. Note that the unit cost of a product equals 70% of its unit price.

1. Click the Query button and then the New button in the Database window.
2. Click New Query in the resulting dialog box to open the design window for a new query.

3. From the Add Table dialog box, select Categories, Products and Suppliers tables. Click Close to close the Add Table dialog box.

   
4. Drag the Category Name and Description fields from the Categories field list to the first and second column in the grid. Then drag the Product Name fields from the Products field list to the third column. Finally, drag the * field from the Suppliers field list to the fourth column.
5. In the fifth column of the grid, type Cost:[Unit Price]*[Units in Stock]*0.7 .
6. Select the Criteria row in the Category Name column and enter Beverages.

7. Run the query by clicking the Run button (!) in the tool bar.

   
8. To print the result, choose Print command from the File menu.
9. To save the query, choose Save command from the File menu.