Using a Query Wizard

Every time you have clicked the Query Wizard button in the Other group on the Create tab, you have seen the interesting query wizard entries. You can use query wizards to help you build certain types of “tricky” queries such as crosstab queries (discussed later in this chapter) and queries to find duplicate or unmatched rows. For example, you could have used a query wizard to build the query shown in Figure 8–9 to locate employees who have no room reservations. Let’s use a query wizard to build a query to perform a similar search in the ContactsDataCopy.accdb sample file to find contacts who don’t own any products.

To try this, click the Query Wizard button in the Other group on the Create tab. Select the Find Unmatched Query Wizard option in the New Query dialog box, as shown in Figure 8–16, and then click OK.

Figure 8–16: Select a query wizard in the New Query dialog box.

The wizard opens a page with a list of tables from which you can select the initial records, as shown in Figure 8–17. If you want to use an existing query instead of a table, select the Queries option. If you want to look at all queries and tables, select the Both option. In this case, you’re looking for contacts who haven’t purchased any products, so select the tblContacts table and then click the Next button.

Figure 8–17: You can select tables or queries on the first page of the Find Unmatched Query Wizard.

On the next page, select the table that contains the related information you expect to be unmatched. You’re looking for contacts who have purchased no products, so select the tblContactProducts table and then click the Next button to go to the next page, shown in Figure 8–18.

Figure 8–18: This page is where you define the unmatched link.

Next, the wizard needs to know the linking fields between the two tables. Because no direct relationship is defined between tblContacts and tblContactProducts, the wizard won’t automatically choose the matching fields for you. Click on the ContactID field in tblContacts and the ContactID field in tblContactProducts to select those two fields. Click the <=> button between the field lists to add those fields to the Matching Fields box. Click Next to go to the page shown in Figure 8–19.

Note 

The Find Unmatched Query Wizard can work with only the tables that have no more than one field that links the two tables. If you need to “find unmatched” records between two tables that require a join on more than one field, you’ll have to build the query yourself.

Choose the fields you want to display (see Figure 8–19) by selecting a field in the Available Fields list and then clicking the > button to move the field to the Selected Fields list. The query will display the fields in the order you select them. If you choose a field in error, select it in the list on the right and click the <, button to move it back. You can click the >> button to select all fields or the << button to remove all fields. When you’re finished selecting fields, click Next. On the final page, you can specify a different name for your query. (The wizard generates a long and ugly name.) You can select an option to either view the results or modify the design. So that you can see the design first, select Modify The Design and then click Finish to open the Query window in Design view. Figure 8–20 shows the finished query to find contacts who have purchased no products.

Figure 8–19: On this page you select the fields to be displayed in a query.

Figure 8–20: The query wizard has helped you build a query that finds contacts who have purchased no products.

If you run this query, you’ll find three contacts who haven’t bought anything. Perhaps you should schedule a call to these people to find out why!