Ms Office Tune Up

Design view’s usually the best place to start constructing queries, but it’s not the only option. You can use the Query wizard to give you an initial boost, and then refine your query in Design view.

The Query wizard works by asking you a series of questions, and then creating the query that fits the bill. Unlike many of the other wizards in Access and other Office applications, the Query wizard’s relatively feeble. It’s a good starting point for query newbies, but not an end-to-end performer.

Here’s how you can put the Query wizard to work:

1. Choose Create Other Query Wizard.

   Access gives you a choice of several different wizards (Figure 6-10).

   
   Figure 6-10. In the first step of the Query wizard, you choose from a small set of basic query types.

2. Choose a query type. The Simple Query wizard’s the best starting point for now.

   The Query wizard includes a few common kinds of queries. With the exception of the crosstab query, there’s nothing really unique about any of these choices. You’ll learn to create them all using Design view:

   • Simple Query Wizard gets you started with an ordinary query, which displays a subset of data from a table. This query’s the kind you created in the previous section.

   • Crosstab Query Wizard generates a crosstab query, which lets you summarize large amounts of data using different calculations. You can find more on this advanced topic in Access 2007: The Missing Manual.

   • Find Duplicates Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that shows only records that share duplicated values. If you forgot to set a primary key or create a unique index for your table (Section 4.1.3), then this can help you clean up the mess.

   • Find Unmatched Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that finds unlinked records in related tables. You could use this to find an order that isn’t associated with any particular customer. You’ll learn how this works in Section 6.3.2.1.

3. Click OK.

   The first step of the Query wizard appears.

4. In the Tables/Queries box, choose the table that has the data you want. Then, add the fields you want to see in the query results, as shown in Figure 6-11.

   For the best control, add the fields one at a time. Add them in the order you want them to appear in the query results, from left to right.

   You can add fields from more than one table. To do so, start by choosing one of the tables, add the fields you want, and then choose the second table and repeat the process. This process really makes sense only if the tables are related. You’ll learn more in Section 6.3.

5. Click Next.

   If your query includes a numeric field, the Query wizard gives you the choice of creating a summary query that arranges rows into groups, and calculates information like totals and averages. If you get this choice, pick Detail and then click Next.

   The final step of the Query wizard appears (Figure 6-12).

   
   Figure 6-11. To add a field, select it in the Available Fields list, and then click the > arrow button (or just double-click it). You can add all fields at once by clicking the >> arrow button, and you can remove fields by selecting them in the Selected Fields list and then clicking <. In this example, three fields are included in the query.

   
   Figure 6-12. In the last step, you choose the name for your query, and decide whether you want to see the results right away or refine it further in Design view.

6. Supply a query name in the “What title do you want for your query?” box.

7. If you want to fine-tune your query, then choose “Modify the query design”. If you’re happy with what you’ve got, then choose “Open the query to view information” to run the query.

   One reason you may want to open your query in Design view is to add filter conditions (Section 3.2.2) to pick out specific rows. Unfortunately, you can’t set filter conditions in the Query wizard.

8. Click Finish.

   Your query opens in Design view or Datasheet view, depending on the choice you made in step 7. You can run it by choosing Query Tools | Design Results Run.

Popularity: 3% [?]