Using the Simple Query Wizard

The easiest way to create a query is with the Simple Query Wizard, which enables you to select the table fields you want to include in the query. A simple query is useful when you want to weed out extraneous fields but still want to see every record in the database table. The Simple Query Wizard helps you create a select query .

Select Query The select query is used to select certain data from a table or tables. It not only filters the data, but it can also sort the data. It can even perform simple calculations on the results (such as counting and averaging).

To create a select query with the Simple Query Wizard, follow these steps:

1. In the Access window, open the database you want to work with and select the Queries icon in the database window.

2. Double-click the Create Query by Using Wizard option found in the database window. The first dialog box of the Simple Query Wizard appears (see Figure 15.1).

   Figure 15.1. The Simple Query Wizard first asks what fields you want to include in the query.

   

3. Choose the table from which you want to select fields from the Tables/Queries drop-down list.

4. Click a field name in the Available Fields list; then click the Add > button to move the field name to the Selected Fields list. Add fields as needed, or move them all at once with the Add All >> button.

5. (Optional) Select another table or query from the Tables/Queries list and add some of its fields to the Selected Fields list (this enables you to pull data from more than one table into the query). When you have finished adding fields, click Next .

   

   Relationships Required If you're going to use two or more tables in your query, they must be joined by a relationship. See Lesson 9, "Creating Relationships Between Tables," for more information.

   
   

6. The next screen asks if you want to create a detail or summary query. A detail query lists all the fields that you selected in step 4 and 5. A summary query allows you to summarize data in numerical fields using the formulas sum (total), avg (average), max (maximum), and min (minimum).

7. (Optional) To summarize field data using a formula, click the Summary option button. Then click Summary Options . Any fields containing numerical data will be listed on the Summary Options screen. In our example, you could summarize the data by the AmountSpent field (as shown in Figure 15.2). Use the formula check boxes (such as sum or avg) to select the calculation that will be used to summarize the field data in the query. Then click OK . Click Next to continue.

   Figure 15.2. You can summarize data in the query such as totaling the values (using the SUM formula) in a particular field.

   

8. On the next screen, enter a title for the query. Then, click Finish to view the query results. Figure 15.3 shows the results of a simple query.

   Figure 15.3. Queries such as this detail query can be created using the Simple Query Wizard.

   

The problem with queries created using the Simple Query Wizard is that you do not have the option of setting sort parameters for the records or the capability to filter them by particular criteria. Simple queries just allow you to select the fields. For this query to provide a little more manipulation of the table data, you would have to edit this in Query Design view, which is discussed in the next lesson. Building queries from scratch provides you with a lot more control over how the data is filtered, sorted, and summarized.

Saving a Query

When you create a query, Access saves it automatically. You don't need to do anything special to save it. When you are finished viewing the results of the query, click its Close ( X ) button. The new query is then listed in the Query list that the database window provides.

Rerunning a Query

At any time, you can rerun your query. If the data has changed in the table fields that you included in a query, rerunning the query provides you with an updated set of results.

To rerun a query, follow these steps:

1. Open the database containing the query.

2. Select the Queries icon in the database window.

3. In the Query list, double-click the query you want to run, or click it once and then click the Open button.

Query Results Look Like Tables Query results can be manipulated in the Datasheet view just like a table. You can use the Sort and Filter features on the results, or you can delete records from them. You cannot add data to a query, however, as you can to a table (unless you use the Create Table Query and make a new table from the query results).