Access XP

Running Database Queries

Introduction

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

  • Run an existing query
  • Create a single-table query
  • Create a multi-table query

Run an existing query

Like tables and forms, a query is a type of database object in Access 2002 XP. A query is a search for records that match the exact criteria you define. In this example, we will run a query against the Contacts table and list all records found by Last Name, First Name, and Work Phone.

To run an existing query:

  • Open the contact management database.
  • In the database window, choose the Queries tab from the Objects palette.
  • To open a query, double-click the query title, click once on the query title and then click the Open button, or right-click the title and choose Open from the shortcut menu.

    Contacts Query Selection under Queries Object
  • The query searches the database and then displays the results on the screen.

    Query Results

Creating a single-table query

In this example, we will create a new query and run it against the Contacts table. We will type the following command in the table: Show me the mailing address of all records in the Contacts table . When we create the query, we need to select the following fields in the Contacts table: Last Name, First Name, Address, City, State/Province, and Postal Code.

To create a simple query:

  • Open the contacts management database.
  • In the database window, choose the Queries tab from the Objects palette.
  • Select the Create query by using wizard option and click Open .

    Create Query Selection Under the Queries Palette
  • The Simple Query Wizard opens.

    Simple Query Wizard
  • From the Tables/Queries drop-down list, choose the table or query containing the fields you want to include in the query.

    Tables/Queries drop-down in Simple Query Wizard

Creating a single-table query

  • The Available Fields text box displays all of the fields contained in the table selected in the Tables/Queries field. Select the fields to be used in the new query. You can select one or more and can even include all fields in the query.

    Select Fields To Be Used In The Query

    Click to highlight the first field to be included in the query—Last Name, for example—and click the right arrow button. Repeat until you have selected all fields to be used (First Name, Address, City, State/Province, and Postal Code).

    Fields Included In The Query
  • If the fields you selected include a number field, you are 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 .
  • Type a name for the query (e.g., Contacts Mailing Address) in the What title do you want for your query? field.

    (Leave the Open the query to view information radio button turned on.)

    Assign a Name to the Query
  • Click Finish to run the query.

Creating a multi-table query

Queries are not confined to just a single table. You can create a query that runs against multiple fields in multiple tables. This query is created in an identical manner to the single-table query defined on the previous page. The only difference when creating a multi-table query is that after selecting the fields in one table as we saw in the last example, you then select the next table and choose additional fields.

In this query, we will ask for the name, contact type, and phone number of all records in the Contacts table. When we create the query, we will select fields from two tables: Contacts table (Last Name, First Name, and Work Phone fields) and Contacts Type table (Contact Type field).

To create a multi-table query:

  • Open the contacts management database.
  • In the database window, choose the Queries tab from the Objects palette.
  • Select the Create query by using wizard options and click Open .
  • The Simple Query Wizard opens.
  • From the Tables/Queries drop-down list, choose the first table where you would like to perform the query (e.g., Contacts).

    Tables/Queries drop-down in Simple Query Wizard
  • From the Available Fields , select the fields to be included from this table (e.g., Last Name, First Name, and Work Phone).

    Select Fields to be Included in the Query

Creating a multi-table query

  • Select the next tables or query from the Tables/Queries drop-down list and pick the fields in that table in which you would like to perform the query.

    Selected Fields
  • Type a name for the query (e.g., Contacts by Contact Type) in the What title do you want for your query? field.

    Name the Query
  • Click Finish to run the query.

    Results of the Query

Sorting, finding, and filtering query results

Throughout this tutorial, we have learned several ways to quickly locate information in the contact management database: sorting records, finding records, filtering records, and performing queries. Can you run a query and then sort, find, or filter records? Absolutely. This is the power of a database.

Everything you previously learned about how to save sorts and filters applies to queries as well. Once a query is defined, you can come back to the database at any time—even after new contact records have been added—and run that query.

After you run a query and define a sort, you will be asked to save the sort and the query design when you close the query or exit Access. If you save your changes, the sort will be saved to the query the next time you run the query. If you do not save changes, the query returns to the original order the next time you run it.

Before and After Results of Sort

When a filter is applied to a query, Access will ask if the changes to the query design will be saved the next time you close the database or exit Access.

  • An answer of Yes will save the filter but not the filtered query. The next time the query is run, all records that match the query are visible.
  • To apply the saved filter, click the Apply Filter button to reapply the filter. Remember, Access saves only the last filter you create.

Challenge!

  • Open the Contacts table in Datasheet view.
  • Use the wizard to create a single-table query :
    • Select the Contacts table.
    • Select the following fields in the Contacts table: Last Name, First Name, Address, City, State/Province, Postal Code, Work Phone, Work Extension, and Email Name.
    • When prompted to name the query, call it Address Book Contacts .
  • Use the wizard to create a multi-table query :
    • Select the following fields in the Contacts table : Last Name, First Name, Company Name, Work Phone, and Work Extension.
    • Select the following fields in the Calls table : Call Date, Subject, and Notes.
    • Select a Detail query.
    • When prompted to name the query, call it Job Search Progress .
  • Build onto the sorting, finding, and filtering capabilities learned in previous lessons by applying these techniques on the queries you have created in this challenge.
  • Close the Contacts table. If displayed, click No in response to the Do you want to save changes to the design of table 'Contacts'? system prompt.