Creating Queries

In Access, you use queries to ask questions about your data, to extract complete or partial records from the tables, and even to edit records. Access provides two categories of queries: select queries , which you use to find and extract information from a database; and action queries which you use to update, delete, and otherwise manipulate records. In this topic, you ll look at simple select queries.

Information about  

Append queries, page 267 Update queries, page 268

Finding Specific Fields

Suppose Tip Top Roofing s job supervisor has asked you for a list of the phone numbers of company employees. He is not interested in any other information about the employees ; he just wants names and phone numbers . This is a perfect job for a select query. Access answers a select query by identifying the subset of records and fields that meet the query s criteria and placing the subset in a temporary table called a query datasheet . Follow these steps to create the list:

1. If the Customers table isn t already selected in the Database window, click it in the Tables list.

2. On the Access toolbar, click the New Object button s down arrow, and click Query in the drop-down list.

   The New Object button changes appearance to match the last item created, so it might not match the button we show. Access displays the New Query dialog box shown in this graphic:

   

3. Click Simple Query Wizard , and then click OK .

   The other options in the New Query dialog box guide you through the process of creating different types of queries. The Find Duplicates Wizard and Find Unmatched Query Wizard help you set up queries to find potential errors in the design of your database.

   Access displays the first page of the Simple Query Wizard, as shown in this graphic:

   

4. Select Table: Employees in the Tables/Queries box.

   Access displays the available fields for the Employees table in the Available Fields list.

5. Double-click LastName , FirstName , and HomePhone to move these fields to the Selected Fields list. Then click Next .

6. Enter Phonelist as the query s title, leave the Open the query to view information option selected, and click Finish .

   Access runs the query and displays the Query window shown in this graphic:

   

7. Print the query datasheet by clicking the Print button on the toolbar.

   You now have a printed list of phone numbers to give the job supervisor.

8. Click the Close button to close the Query window.

The Queries list in the Database window now includes Phonelist as an existing query. Any time you want to access the datasheet that lists the names and phone numbers of Tip Top Roofing s employees, you can click the Queries icon on the Objects bar, select this query, and click Open. Access will run the query again and display a new datasheet of results. If you have made changes to the Employees table, these changes will be reflected in the new datasheet.

Finding Specific Records

You now know how to ask questions that require Access to select fields from a table. What if you want Access to select fields only from specific records? For example, suppose the shingles for job 029525A are going to be delivered late and you need the employees assigned to that job to report to the office instead of to the job site in the morning. Follow these steps to modify the Phonelist query so that Access will include in the query datasheet only the names and phone numbers of the employees assigned to job 029525A:

1. In the Queries list, double-click Create query in Design view .

   The Show Table dialog box appears, as shown in this graphic:

   

2. On the Tables tab, click Employees , click Add , and then click Close .

   The Query window opens in Design view, as shown in this graphic:

   

   In the top half of the window, an Employees box lists the fields in the Employees table. (The * at the top of the list represents all the fields.) The bottom half is a table grid called the query by example grid, or QBE grid , in which you can visually construct the query.

3. In the Employees box, double-click these fields: LastName , FirstName , HomePhone , and JobID .

   You are telling Access to include these field values in the results of your query. Access transfers the four fields to the QBE grid s Field row, identifies the fields tables in the Table row, and selects the check boxes in the Show row to indicate that the field values will appear in the query datasheet.

4. Click the Show box for the JobID field to deselect it.

5. Click the Criteria row of the JobID column, and type 029525A .

   The query now looks like the one shown in the graphic on the following page.

   

   You are telling Access to give you the names and phone numbers from the records that have the value 029525A in the JobID field. (The JobID field value will not appear in the query datasheet because that field s Show box is not selected.)

6. Run the query by clicking the Run button on the Standard toolbar.

   Access displays the new Phonelist datasheet shown in this graphic:

   

7. Close the Query window, saving the query as Job 029525A when prompted to do so.

Wrong field name ?

If you double-click the wrong field name, press the Delete key to delete the highlighted entry, and then try again.

 

Editing a Query Datasheet

If you entered the information for employee number 37 exactly as it is shown earlier, the query datasheet indicates a typo in the third record. ( Jefrey should be Jeffrey .) Correcting the record in the query datasheet will automatically correct it in the table. Follow these steps:

1. Open the Job 029525A query. Click an insertion point to the right of the f in Jefrey , type f , and press Enter to complete the record.

   Access saves your change as soon as you move to a different record.

2. Close the Select Query window.

3. Open the Employees table and verify that the record for employee number 37 has been updated.

4. Close the table.