CCNC/CCNC Module 5/Retrieving data with queries/Basic Query Operations/Query Wizard

---

Using the Query Wizard

Outcomes

Upon completion of this tutorial the learner will be able to: Use the query wizard Set the query table and fields to be displayed Select the queries sort order Apply additional criteria Rename column headings for the query Run the query and review the results

Starting the wizard

Figure 1. The wizard task

Using the query wizard can be a very helpful way to quickly build a query. To start the query wizard select the "Use Wizard to Create Query..." task.

Choosing the tables and fields

Figure 2. Choosing the tables

To start building the query select the tables you would like to retrieve data. Be sure to selected tables that are related.

Figure 3. Choosing the fields

Once the table or tables and their field names are displayed in the "Available fields" column move at least one of the fields to be displayed in the query by using the > button. The >> button can be used to move all the fields and the < and << buttons can move fields out of the "Fields in the Query" column. Once all the query fields have been selected click the Next button to continue with the wizard.

Choosing the sort order

Figure 4. Sorting

To assign sort orders to the query select up to four fields to sort upon. Choose the field to be sorted and the order, either ascending or descending. The sort will occur with the first field being the priority sort with the subsequent fields sorting within the previous sort. As an example if last_name was the first sort and birth_date was the second, the query will order the results to be ordered by last_name and then staying within each grouping of last_names they would be sorted by birth_date.

Setting the criteria

Figure 5. Criteria

Setting the criteria is like using a filter. The criteria will "hide" some of the data from the query results. To set the criteria; select a field, choose a condition and enter a value to compare.

Setting the column names

Figure 6. Column Names

It can be useful to change the name of the column when it is displayed in the results. The aliases step of the wizard allows this change, in the right column enter a new name to be displayed.

Reviewing and naming the query

Figure 7. Query Name

The last step of the wizard prompts for a query name. This name will be saved to the database for later retrieval and will be displayed as a save query. This step of the wizard also provides a summary of the query, selecting the "Modify Query" option would allow editing of the query summary text. If changes to the query are required it would be better to use the "Back" button of the wizard and make the changes using the wizard.

Running the query

Figure 8. Execute the Query

To run the query, right-click the query name used when the query was saved and select "Open".

Figure 9. Query Results

Review the results to confirm they are what was desired. If changes are required the query will have to be opened in design mode.

Test your knowledge

Using the Query Wizard

1. Setting the criteria in the wizard is like using a query filter? TRUE → Absolutely Correct! FALSE → Incorrect, criteria are like filters. Click here to review the topic. 2. Which step of the query wizard CANNOT be ignored? (a) Field Selection → Absolutely correct! (b) Sorting Order → Incorrect, Field Selection is the only step that cannot be ignored. Click here to review the topic. (c) Search Conditions → Incorrect, Field Selection is the only step that cannot be ignored. Click here to review the topic. (d) Grouping Conditions → Incorrect, Field Selection is the only step that cannot be ignored. Click here to review the topic. (e) Aliases → Incorrect, Field Selection is the only step that cannot be ignored. Click here to review the topic. Your score is 0 / 0