MS Access 2003 - Lesson 22: Using Query Wizards

Access includes 4 different types of Wizard queries, and only one where you can see all of those query types. Select Query from the Insert menu, Access displays the New Query dialog box as shown in Figure 1.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 1
Figure 1: New Query dialog box.

Notice that there are 5 options listed in the dialog box. The first option (Design View) is not really a query, it allows you to create a query from a custom mixture. The purpose of the remaining 4 options is as follows:

• Simple Query Wizard . This option allows you to create queries in the simplest way. This wizard is the same as when you double-click the Create Query By Using Wizard in the Database window (Figure 2).

MS Access 2003 - Lesson 22: Using Query Wizards Picture 2
Figure 2: Database window

• Crosstab Query Wizard . This option creates a simple Crosstab query, shown at the beginning of the chapter.

• Find Duplicates Query . Create a select query that displays the same records in a table.

• Find Unmatched Query . Create a query that displays records that have no relationship to records in different tables. (This query is easy to use when working with relational databases, a concept that will be introduced in Chapter 13, ' Understanding data relationships').

The query you choose will determine which Wizard is used by Access. In the following sections, you will learn how to use three of the 4 Wizards given here.

Create a simple query

To create a simple query, select Simple Query Wizard from the New Query dialog box, then click the OK button. Similarly, you can also double click on the Create Query By Using Wizard option available when the Queries button is selected in the Database window. Then, Access displays the Simple Query Wizard dialog box as shown in Figure 3.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 3
Figure 3: Simple Query Wizard dialog box.

On the left side of the Simple Query Wizard dialog box, you can use the drop down list to determine which sources Access should use for the new query. Note that you can use sources as tables or other queries. When the Customers database has only one table (Business Custormers table), Access selects it for you by default. If you are working with a database that contains tables and other queries, you can use the drop-down list to change the data sources that the Wizard uses.

In the lower left part of the dialog box is a list of all the fields defined in the selected table or query. If you change the data source in the entire list (described in the preceding paragraph), the available fields will change accordingly. You can use the list and controls on the right side of the list, to indicate which fields you want to be used on the query you are creating.

To illustrate this example, select the Company Name , Address 1 , Address 2 , City , State , and Zip code fields. Make sure that after selecting each field, you must click your mouse on the> button. Then, Access displays the name of each field in the Selected Fields list at the bottom right of the dialog box. When selecting the fields, the dialog box appears as shown in Figure 4.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 4
Figure 4: Simple Query Wizard dialog box after selecting some fields.

With the fields selected, you are ready to proceed to the next step in the Wizard. Click your mouse on the Next button, Access displays the dialog box shown in Figure 5. This is the last dialog box in this Wizard.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 5
Figure 5: The final dialog box of the Simple Query Wizard.

At this interface, you can enter a name you want to use to save this query. The suggested name is simply the table name on which the query is based (Business Customers) with the word 'Query' added. The simple query you created using the previous dialog can be used for mailing labels, since it only contains address information for the list of customers. Enter the name Address List, then click your mouse on the Finish button. Access displays that query result as shown in Figure 6.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 6
Figure 6: Results of Simple Query Wizard.

Create a Crosstab query

To create a Crosstab query, first open the New Query dialog box by selecting Query from the Insert menu. From that dialog box, select the Crosstab Query Wizard and click your mouse on the OK button. Access displays the Crosstab Query Wizard dialog box shown in Figure 7.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 7
Figure 7: The Crosstab Query Wizard dialog box.

At the top of the Crosstab Query Wizard dialog box, Access asks you which table you want to query the data to. Because this database has only one table (Business Customers), Access has selected it for you. If you are working with a database that contains tables and other queries, you can use the View area in the middle of the dialog box to change the data source that the Wizard offers.

At the bottom of the dialog box, the Wizard displays the completed Crosstab query. Access updates this presentation every time you make changes while using the Wizard. To switch to the next dialog box, click your mouse on the Next button (Figure 8).

MS Access 2003 - Lesson 22: Using Query Wizards Picture 8
Figure 8: Select lines.

Crosstab queries allow you to determine the results displayed in both rows and columns. Using the Wizard, you should specify the fields you want to appear in each line.

For example, suppose you want to tabulate the number of active and inactive accounts. In the Available Fields list, you should select the Salesperson field and click your mouse on the > button. Note that the Wizard updates the crosstab template at the bottom of the dialog box.

Next, you should select the State field and click your mouse on the > button. Next, the Wizard updates the crosstab template at the bottom of the dialog box as shown in Figure 8.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 9
Figure 9: Query crosstab after selecting row title

You only follow Access to display salespeople and State on the left side of crosstab. Click your mouse on the Next button. Access displays the dialog box shown in Figure 10.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 10
Figure 10: Select columns

In this dialog box, Access asks you to select the fields that it will display as columns. Since you want this query to indicate how many accounts are active for each vendor, you should select the Active field. After selecting the Active field, notice that Access updates as the form at the bottom of the dialog box. Click your mouse on the Next button to proceed. Access displays the dialog box shown in Figure 11.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 11
Figure 11: Selecting operations for the query.

In this dialog box, Access asks you to determine what query you want to calculate. At the right side of the dialog box, the Functions box lists all the different available operations. You can perform multiple calculations on multiple fields, but some combinations of fields and operations cannot be performed. For example, if you select a text field, the average calculation cannot be done, you should only perform calculations on a numeric or monetary field.

To end this query, select the Company Name field and select the Count calculation. Note that the information in the sample area of ​​the dialog box changes with your selection. The query calculates how well the accounts are active (there is a company name for each account) in each state for each sales representative.

Finally, note that there is a check box you can use to indicate that the query should provide a summary for each line. With a simple crosstab that has only 2 columns (active or inactive), a summary is probably not necessary. Click on the checkbox to turn off the summary. After the selection is completed, you can click your mouse on the Next button. Access will display the dialog box as shown in Figure 12.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 12
Figure 12: Access asks you about the query name

In this dialog box, you complete the query creation and need to save it. Access asks you the name to use for this query. For names for other parts of the database, you should use some of the names described, such as Active Account Tally. Type in the name and click your mouse on the Finish button. Access will save the query and update the query list in the Database window, then open the query to display the results that have just been made. Figure 13 shows when the query is completed.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 13
Figure 13: Results of the query.

When you look at crosstab, one of the first things that you notice is that the columns are -1 and 0. Remember, the Active field is a field with a Yes / No data type, which means that Access stores the value. This field is like one of two numbers: -1 and 0. Thus, these numbers are also displayed in the title. After this chapter, you will learn how to change the title by changing the properties associated with the query.

If you look at this strange case in the title section, you can see that crosstab provides the information you requested. It displays the account status on active (Yes or -1) and inactive (No or 0) for each state and rep. Crosstab Query Wizard is easy for you to create data queries.

Create a Find Duplicates Query

You can also use the New Query dialog to create a Find Duplicates query, which allows you to quickly search for a table with identical fields. Select the Find Duplicates Query Wizard in the dialog box and click your mouse on the OK button. In turn, Access displays the Find Duplicates Query Wizard dialog box as shown in Figure 14.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 14
Figure 14: Find Duplicates Query Wizard dialog box.

In this dialog box, Access lists the data sources you can use for the query. Normally, you use a table as a data source, but with this Wizard you can use another query as well. Because the Business Customers table is unique in the Customers database, Access has selected this table for you. Click your mouse on the Next button to move to the dialog box shown in Figure 15.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 15
Figure 15: Select a field to check.

In this dialog box, Access asks you which fields to check for duplicate values. Although there is no limit to the number of fields you can have in query checking, you should only select the fields needed to make sure there are no duplicates. In the case of the Business Customers table, simply check the Company Name and Contact fields to determine if there are duplicates.

First, select the Company Name field and click your mouse on the > button. Next, select the Contact field and click on the > button. Access displays two fields in the order in the list to the right of the dialog box. After you have defined the fields, click your mouse on the Next button. Access displays the dialog box shown in Figure 16.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 16
Figure 16: Displaying additional information.

In this dialog box, Access asks if you want the query to display more information. When reviewing duplicate records, you can use this information to help decide which records you want to save and which records you want to delete. For the Business Customers table, you want Access to display the Contact Phone , Last Order , Salesperson fields. Select a few fields (remember to click the > button after selecting each field). Access adds the name of each field to the list to the right of the dialog box. When done, click your mouse on the Next button, Access displays the dialog box shown in Figure 17.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 17
Figure 17: End of query.

Now that you have completed the query creation, Access asks you to name the query. From the Wizard's suggestion for the name of this query, click your mouse on the Finish button to accept the name. Access saves the query under this name and updates the query list in the Database window. Access opens the query to display the completed result as shown in Figure 18.

MS Access 2003 - Lesson 22: Using Query Wizards Picture 18
Figure 18: Results of Find Duplicates query.

Now you can review the duplicate records and decide what you want to do with them. If you delete any record in the screen, Access will delete the corresponding record in the table below (Business Customers). For this reason, Find Duplicates queries are very helpful in managing the information in the table.