Running Database Queries of Access 2003

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 (continued)

      • 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 (continued)

          • 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.