Running the Query Wizards

A query is really just a question. You ask the question, and Access returns an answer in the form of data. For instance, you might ask Access which medicinal plants came from a particular catalog. That's a very specific question, and Access will return only those plants that are tagged as medicinal plants and were also ordered from the catalog you specify. This type of query is known as a Select query Access retrieves and displays the records that answer your questions.

The Simple Query Wizard you learned about in Chapter 7, "Retrieving Data with Queries," is only one of several query wizards. There are three more query wizards:

• The Find Duplicates Query Wizard

• The Find Unmatched Query Wizard

• The Crosstab Query Wizard

These three queries make easy work of some rather complicated tasks .

The Find Duplicates Query Wizard

Before we can show you how to use the Find Duplicates Query Wizard, we must define just what a duplicate is. Some fields can repeat entries, and some can't. For instance, you learned in Chapter 4, "Planning a Database," and Chapter 6, "Tapping the Power of Relationships," that every entry in a primary key field must be unique, and you learned why. Furthermore, some fields require a unique index , and these types of fields accept only unique entries. You can learn more about indexes in Chapter 11, "Customizing Your Tables."

On the other hand, some fields repeat many entries. For instance, the CatalogName field in the Plants table repeats catalog names , and the Country field in the Catalogs table repeats the USA entry. You'll find several repeated values in your tables.

Duplicate entries can mean duplicate records. That isn't always the case, but it's something you'll need to check for because duplicate records can cause errors. For instance, suppose you have a table of orders and one of those orders is entered twice. As a result of that mistake, your customer might receive a duplicate invoice or be billed twice the appropriate amountand that isn't good business.

A duplicate record is one in which every field is the same except for the primary key field if you're using an autonumber data type. (You can learn more about the autonumber data type in Chapter 11.) Or, the repeated values could be in just a few critical fields. For example, suppose your gardening database contains hundreds of plants, which makes keeping up with just which plants you've entered and which plants you haven't difficult. Several hundred plants later, you reenter Purple Coneflower. You might use a different picture, and you might even enter a different purchasing catalog, but the common and Latin names are the same. Such an entry could constitute a duplicate record, even though all the data isn't exactly the same.

Right now, our examples don't have any duplicate records, so we'll have to create one, just so we can find it with the Find Duplicates Query Wizard. To that end, open the Catalogs table and enter a new record for Gurney's, repeating all the same data, except for the name entry. This time, enter the catalog name as Gurneys (without the apostrophe). Remember, this field is the primary key, so every entry must be unique.

Now, follow these steps to launch the wizard and see whether it can find the duplicate record:

1. Select Query from the Insert menu; then double-click Find Duplicates Query Wizard in the New Query dialog box shown in Figure 12.1. Or, select the query item and click OK .

   Figure 12.1. Select the Find Duplicates Query Wizard.

   

2. Figure 12.2 shows the query's first pane, which defaults to the Catalogs table (because it's the first table in the database). That's the table you need, so click Next to continue.

   Figure 12.2. Selecting the Catalogs table.

   

3. To search for duplicates on the Name field, double-click Name in the Available Fields list to add it to the Selected Fields list, as shown in Figure 12.3. Click Next to continue.

   Figure 12.3. Searching for duplicate Name entries.

   

4. In the next pane, you can add a second field to help you identify any records that are found. However, because you're working with the catalog names, that step isn't necessary, so just click Next .

5. In the final window the wizard assigns a default name to the query. Accept this name, and click Finish to display the results shown in Figure 12.4.

   Figure 12.4. The query says no duplicate entries exist in the Name field.

   

Are you surprised by the results? Oddly enough, the query doesn't return any records. You know you entered a duplicate recordin fact, you did so on purpose. Even so, the wizard isn't wrong. If you remember, you purposely entered the name a little differently the second time (without the apostrophe). The wizard can't tell that Gurney's and Gurneys really are the same because as far as data is concerned , they aren't. You didn't actually compare every fieldyou only searched for duplicates in the Name field, and the wizard didn't find any.

This time, let's rely on a different field and see what happens. Repeat steps 15, but this time, look for duplicates in the Address field instead of the Name field. In addition, in step 4, specify the Name field; then name the query CatalogDuplicates in the wizard's last window.

The results of this query are shown in Figure 12.5. This time the query returned two records because the address 110 Capital Street appears twice in the table. Including the Name field allowed you to determine which catalog you entered twice.

Figure 12.5. This time the query found two duplicates in the Address field.

You're not limited to searching just one field as you did in both examples. You can search for duplicate entries in many fields. For instance, you might want to see whether any employees share the same first and last name. In that case, you'd search on both the first and last name fields. You can even include every field in the table, except for the primary key field. Because that field always contains a unique entry, the wizard would never find any duplicates, even if every other value in the record was exactly the same as at least one other record.

You might want to try a few more examples by finding duplicate Country entries or City entries. When you're done, be sure to delete the duplicate record for Gurneys in the Catalogs table.

The Find Unmatched Query Wizard

The Find Unmatched Query Wizard finds records not related to another table in the same database. For instance, you might want to see which customers haven't placed an order or which employees haven't taken any sick days. You could build these queries yourselves, but often, the Find Unmatched Query Wizard is quicker.

Now, let's use this wizard to find catalogs with no matching record. In other words, let's find catalogs from which you've not ordered. To do so, follow these steps:

1. Launch the wizard by selecting Query from the Insert menu and double-clicking Find Unmatched Query Wizard (refer to Figure 12.1).

2. In the wizard's first pane, select the table that contains the primary key records. In this case, that's the Catalogs table, but because that table is selected by default, you can just click Next .

3. In the next window, select the table that contains the related records, or the foreign key values. In this case, that's the Plants table. To continue, click Next .

4. The wizard wants to know which field contains the related values and will attempt to select the right fields. Figure 12.6 shows that the wizard's guess was partially correct. The wizard correctly guessed that the Name field in the Catalogs table is the related field. However, it guessed that the CommonName field in the Plants table is the other related field. This isn't right, so you'll have to fix the guess to proceed. In the Plants list, change the selected field from CommonName to CatalogName , and click Next when you're ready to continue.

   Figure 12.6. The wizard guesses which fields are related between the two tables.

   

5. In the next window, identify the fields you want displayed in the query's results. We just need to see the Name field, as shown in Figure 12.7. Click Next .

   Figure 12.7. You need to view only the Name field in the query's results.

   

6. The last window assigns a name to the query. Accept this name by clicking Finish to display the query's results shown in Figure 12.8.

   Figure 12.8. The wizard found two catalogs from which you haven't ordered yet.

   

The wizard found two catalog entries that have no related plant recordsRaintree Nursery and Territorial Seed Company. If you open the Plants table, you can quickly see that this is true. On the other hand, if you had several plant records to review, you wouldn't want to depend on visual confirmationthe wizard makes the job much easier.

The Crosstab Query Wizard

A crosstab query is a curious query and can be a bit difficult to create yourself. That's why the Crosstab Query Wizard is such a useful wizard.

In a nutshell , a crosstab query summarizes data by categories. The main claim to fame for this type of query is the amount of data it can display in a small and still easily readable format. For instance, you might want to total the number of orders placed each day. Or, you might want to review the number of sick days used by each employee totaled by the month. In both cases, a crosstab query would do the trick.

Every crosstab query must contain three elements:

• A column heading

• A summary field

• A row heading

We'll point these elements out to you as you use the wizard to create a crosstab query.

Note

If you need to base a crosstab query on more than one table, you must create a query that combines the tables and work from that. Considering the results, this isn't such a big deal. You just need to be aware of the requirement.

Building the Query

The first step to using the crosstab query is usually building a query that contains all the data you want to summarize. Suppose you want to know how many plant types you've ordered from each catalogthe perfect solution is a crosstab query. You want to summarize (total) the type using the catalog name as a category. To create the query on which you'll base your crosstab query, follow these steps:

1. Select the Plants table in the Database window.

2. Select Query from the Insert menu, and double-click Design View in the New Query dialog box.

3. Add the Types table to the Query Design grid by clicking the Show Table button on the Query Design toolbar. Then, double-click Types , and click the Close button.

4. Drag the following fields to the grid: CatalogName, TypeID, and Description, as shown in Figure 12.9.

   Figure 12.9. Add the CatalogName, TypeID, and Description fields to the query.

   

5. Save the query as CrosstabQuery and close it. If you need help creating this query, refer to Chapter 7.

Now you're ready to launch the Crosstab Query Wizard and build the crosstab query. Follow these steps:

1. Select Query from the Insert menu; then double-click Crosstab Query Wizard in the New Query dialog box.

2. In the wizard's first pane, select the query's data source. This time, you're using a query, so click the Queries option in the View section to update the contents of the list control, accordingly . Then, select CrosstabQuery , as shown in Figure 12.10.

   Figure 12.10. Selecting the query that contains the data for the crosstab query.

   

   Notice how the Sample panel at the bottom of the pane displays an example of how the data will be arranged in the actual query results. Pay attention to this panel as you continue and when you're using the query on your own. It'll help you make decisions as you go. Click Next to continue.

3. In the next pane, identify the row heading field. Because you're summarizing plants by their types, select the Description field, as shown in Figure 12.11. That way, the query will display the type text and not the primary key value. Click Next .

   Figure 12.11. Identify the Description field as the crosstab's row heading field.

   

4. In the following window, select the column headings. Figure 12.12 shows the CatalogName field selected. Click Next to continue.

   Figure 12.12. Select the CatalogName field as the crosstab's column heading field.

   

5. Now the query wants you to identify the field you're summarizing, so select TypeID . The wizard also needs to know how to summarize the data in the TypeID field. You can see that you can summarize the data in a variety of ways, from counting or averaging the records to using more advanced statistical functions such as the standard deviation. You just want to count the number of records, so select Count from the Functions list. Figure 12.13 shows the updated Sample panel, which displays the Count(TypeID) expression in the detail section of the query. Click Next .

   Figure 12.13. Summarize the TypeID field by counting the number of entries for each catalog.

   

6. The last dialog box names the query; you won't change it for this example. If you want to view the query's results, select the View the Query option. Otherwise, select the Modify the Design option to open the query in design view, where you can tweak it just a bit if necessary. When you need help, you can select the Display Help on Working with the Crosstab Query check box. Doing so automatically opens the Help window to information on crosstab queries. Don't select that option now, though. In fact, you don't need to change any option; just click Finish to display the crosstab query shown in Figure 12.14.

   Figure 12.14. The wizard displays these records.

   

The first record tells you that you have five decorative plants and that one came from Gurney's, one came from Richters, and three came from Wildseed Farms. The second record summarizes medicinal plants in the same way. There's a total of three, with one plant coming from each of the listed catalogs. Even though you don't have a lot of data to summarize, it's easy to see how this format could be useful when analyzing many records.