Access 2000

Queries

Introduction

By the end of this lesson, you should be able to:

  • Define a query
  • Run an existing query
  • Create a simple select query

What is a query?

In previous lessons, you learned to work with database objects such as tables and forms. A query is another type of database object .

A query is data resulting from questions you ask of one or more tables. For example, let's say you want to find out how many male students are in Mr. Emerson's class.

Students in Mr. Emerson's class

Male students in Mr. Emerson's class

Running an existing query

To run an existing query:

  • Open the Great Lake Elementary School database.
  • In the database window, choose the Queries tab from the Object s palette.
  • To open a query, double-click the title, select the title, and click the Open button, Open button or right-click the title and choose Open from the shortcut menu.

Query dialog box

Creating a simple query

To create a simple query:

  • Open the Great Lake Elementary School database.
  • In the database window, choose the Queries tab from the Objects palette.
  • Select Create Query by using Wizard and click the Open button, Open button double-click it, or right-click it and choose Open from the shortcut menu.
  • The Simple Query Wizard opens.
  • From the Tables/Queries drop-down list, choose the table or query containing the fields you want to include in the query.
  • In the Available Fields text box, select the fields you want to include and click the right arrow or double-click the field to move the fields into the Selected Fields text box.
  • Select any additional tables and queries from the Tables/Queries drop-down list and repeat. Click Next when all fields have been selected.
  • If the fields you selected include a number field, you'll be asked to select a summary or detail query. To see each record , choose Detail . To see sums, averages , etc., choose Summary and set the summary options. Click Next .
  • Provide a name for the query. Click Finish to run the query.

Simple Query Wizard

Sorting, finding, and filtering query results

You can sort, find, and filter the results of a query just as you would other records.

When you sort a query and then close it, you are asked if you want to save the changes to the query design. If you save the changes, the query will still be sorted the next time you open it. If you choose not to save changes, the query returns to its original order.

If you filtered a query and know you want to reuse it, remember to apply the filter just before closing it. Access will ask if you want to save changes to the design of the query. If you answer Yes to save changes, the last filter is saved. When you open the form again, all records will be visible. To view your filter, click the Apply Filter button to reapply the filter. However, Access only saves the last filter you create.

The females_Foushee query after being sorted alphabetically by Student Last Name

Challenge!

  • Open the Great Lake Elementary School database.
  • Choose Queries from the Objects palette.
  • Open the males_Emerson query and sort the query result alphabetically by Student Last Name .
  • Close the query and choose not to save changes.