3 4
The simplest queries retrieve, group, and organize data, but they don’t limit the results or evaluate expressions. You saw two examples of simple queries in the previous sections. Using the Simple Query Wizard might be convenient, but you can create a select query without the wizard. In addition, whether you start with the Simple Query Wizard or build the query from scratch in the query Design view, you can modify a query in a number of ways.
In this section, instead of using a wizard to create the list, let’s use the query Design view.
To create the list without using a wizard, follow these steps:
Troubleshooting - I can’t see the Create Query In Design View item in the Database window
If the Create Query In Design View item isn’t visible in the Database window, the New Object Shortcuts option might be turned off. (The Create Query In Design View item is one of these shortcuts.) To turn the option back on, choose Tools, Options, and select the New Object Shortcuts check box on the View tab of the Options dialog box.
After you open query Design view, the Show Table dialog box should appear automatically, as shown in Figure 9-9. If the Show Table dialog box isn’t displayed, click the Show Table button on the Query Design toolbar.
Figure 9-9. When you begin a new query in Design view, the Show Table dialog box is automatically displayed.
Figure 9-10. The CompanyName, ContactName, ContactTitle, and Phone fields have been added to the design grid.
There are five methods for adding fields to the design grid, as follows:
When you use either of the last two methods and the query is based on more than one table or query that have a field name in common, choose the appropriate table from the Table drop-down list. (If the Table row isn’t displayed, choose View, Table Names.)
Adding Multiple Fields to the Design Grid
You can also select multiple fields in a table and drag them to the design grid. To select a contiguous block, click the first field in the set, hold down the Shift key, and then click the last field in the set. To select a noncontiguous group, hold down the Ctrl key while clicking each field in the field list. Once the desired fields have been selected, drag the group to the design grid. Access will add the fields to the design grid in the same order in which they appear in the field list.
You can also double-click the field list’s title bar to highlight all the fields at once, and drag them to the design grid as a block. This method has some advantages over the asterisk method described earlier. If you drag all the fields to the design grid as a block, you can remove a few fields you don’t need in the query, and you can add criteria to individual fields. But if you drag the asterisk to the design grid, you won’t have to update the query if you add, delete, or change the names of fields in the query’s data sources.
tip - Preload a data source