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:
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 using 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:
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.
Close the wizard's first results and try again. 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. Although you can't tell by viewing the results, the wizard uses a totals view to determine duplicates (see "Summarizing Results Using a Totals View" later in this chapter).
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:
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 area, yet 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:
We'll point out these elements as you use the wizard to create a crosstab query.
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 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:
Now you're ready to launch the Crosstab Query Wizard and build the crosstab query. Follow these steps:
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.