Access XP

Introduction

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

• Run an existing query
• Create a Single-table query
• Create a Multiple-table query

Run an Existing Query

Like tables and forms, a query is another 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 Object palette.
• To open a query, double-click the query title, or click once on the query title and then click the Open button, or right-click the title and choose Open from the shortcut menu.
  
  
• The query searches the database and then displays the results on the screen.
  
  

Creating a Single Table Query

In this example, we will create a new query and run it against that very same 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 Object palette.
• Select the Create query by using wizard option and click the Open button .
  
  
• The Simple Query Wizard opens.
  
  
• From the Tables/Queries drop-down list, choose the table/query containing the fields you want to include in the query.
  
  

Creating a Single Table Query

• The Available Fields text box displays all the fields contained in the table selected in the Tables/Queries field. You are to select the fields to be used in the new query. You can pick one or more, or even all fields in the query.
  
  
  
  Click to highlight the first field to be included in the query—Last Name, for example—and then click the right arrow button. Repeat until you have selected all fields to be used (First Name, Address, City, State/Province, and Postal Code).
  
  
• 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 the Next button.
• 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).
  
  
• Click the Finish button to run the query.

Creating a Multiple-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 multiple-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 Multiple-table Query:

• Open the Contacts Management database.
• In the database window, choose the Queries tab from the Object palette.
• Select the Create query by using wizard options and click the Open button .
• 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).
  
  
• From the Available Fields, select the fields to be included from this table (e.g., Last Name, First Name, and Work Phone).
  
  

Creating a Multiple-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.
  
  
• Type a name for the query (e.g., Contacts by Contact Type) in the What title do you want for your query? field.
  
  
• Click the Finish button to run the query.
  
  

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 Multiple-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 that you have created in this Challenge!
• Close the Contacts table, and if displayed, click the NO button in response to the Do you want to save changes to the design of table 'Contacts'? system prompt.