Using Other Query Wizards

Access's different query features are quite powerful; they can do amazingly complicated calculations and comparisons on data from several tables. Queries also can do calculations to summarize data or arrange the query data in a special format called a crosstab. Creating more advanced queries means that your database tables must be joined by the appropriate relationships; otherwise , the query cannot pull the data from multiple tables.

You can create very complex queries from the Query Design view, which you learn about in the next lesson. However, Access also provides some wizards that can be used to create some of the more complex query types. These wizards include the following:

• Crosstab Query Wizard This wizard displays summarized values, such as sums, counts, and averages, from a field. One field is used on the left side of the Query datasheet to cross-reference other field columns in the Query datasheet. For example, Figure 15.4 shows a Crosstab table that shows the different products that each customer has ordered, sorted on the customer's last name (and then sorted by promised by date). Note that some customers have multiple orders.

  Figure 15.4. Crosstab queries allow you to cross-tabulate information between table fields. This query is sorted on the Last Name field.

  

• Find Duplicates Query Wizard This query is used to compare two tables and find duplicate records.

• Find Unmatched Query Wizard This wizard compares two tables and finds all records that don't appear in both tables (based on comparing certain fields).

You can access any of these query wizards from the database window. With the Queries icon selected, click the New button on the database window toolbar. The New Query dialog box appears, as shown in Figure 15.5.

Figure 15.5. The other query wizards can be accessed from the New Query dialog box.

Select the wizard that you want to use for your query and click OK. Work with the wizard as it walks you through the steps for creating your new query.