Let's say you're building a query from a single field listthat is, one table or one query. The fastest way to get started is to select the table or query that contains the data you want to use in the Database window and choose Insert, Query. In the New Query dialog box, you have several choices for creating a query. The Simple Query Wizard is simpleso simple, in fact, that it doesn't help much in creating queries. The other query wizardsCrosstab (discussed in Chapter 9) and Find Duplicates and Find Unmatched (both described in this chapter)all have their uses. But they will likely represent a minority of the queries you create. Most of the time, you'll use the default Design View selection.
Adding All Fields to the Design Grid
Often you'll want to select specific fields from a field list to include in the query. But sometimes you'll want to add every field in the list. You can add all the fields by using either the "asterisk" method or the "drag-'em-all" method.
When you double-click the asterisk at the top of a field list, you add all the fields in it to the design grid. Although the asterisk occupies only a single column on the grid, all fields are displayed when you run the query.
What do you do if you want to add criteria or sorting? You separately add the field(s) you want to use for criteria or sorts (see Figure 8.2). As indicated earlier, you deselect Show in these columns so the values in the field don't appear twice.
Figure 8.2. The asterisk in the Field row of the first column indicates that all fields in tblCustomers will be included in the query, even though they are not entered individually on the grid. The second column is used just to designate criteria, so the Show box has been deselected.
Let's create a simple query that uses the asterisk to select all fields:
If you set the Output All Fields property to Yes in the Query property sheet, it's the same as adding the asterisk to the design grid. (The Query property sheet can be accessed by choosing View, Properties in Design view.) You might prefer the asterisk method, however, because the design grid is always displayed, whereas the property sheet is usually hidden.
In the "drag-'em-all" method, each field in the field list occupies a separate column on the grid. You start by double-clicking the title bar of the field list, which selects all the fields. You can then drag and drop the fields as a group onto the grid.
This method has the advantage of making it easy to specify criteria and sorts. But there's a huge difference between the methods. With the asterisk, the query is continuously updated for additions and deletions of fields in the underlying tables. With the drag-'em-all method, the query is not updated for those changes.
Create a Query from a Filter
If you create a filter that you'd like to have as a permanent object, you can easily save it as a query. Because a filter displays all fields, however, the Output All Fields property in the filter will be set to Yes. try this exercise:
You can undo actions that you just performed in the Query Design window. If you just edited an entry on the design grid or added a field to a column, choose Edit, Undo Cell Edit. If you just changed a property setting, the Edit, Undo Property Setting command will be available.
The Unique Values property is useful for eliminating duplicate records in a single table. For example, let's say you want a simple list of the states in which you have customers. Follow these steps:
The sort is one of the most important query tools. As you saw with advanced filters, one advantage of using the design grid to apply sorts is that you can combine descending and ascending sorts.
Applying sorts is not difficult in Access, but there is an element of uncertainty because you can set sorts both in the Sort row of the design grid in Design View, and in Datasheet/Form views. Confusion arises when you set a sort in Datasheet or Form view that reverses the sort you set in Design view. If you plan to use a query as the basis of a form or report, you'll find it simpler and more efficient to designate sorts on the design grid. When you're reviewing records in datasheet/form views, use the sort buttons as you need them, but don't save the changes.