Building Queries

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.

Asterisk 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:

1.	Download the NiftyLionsChap8.mdb file to your hard drive.
2.	In the Database window, select the tblCustomers table.
3.	Choose Insert, Query. With Design View selected, click OK.
4.	Double-click the asterisk to add all fields to the design grid. Maximize the window.
5.	Double-click CustLastName to add the field to the grid. You can also add fields to the design grid by drag-and-drop. Within a field list, you can press Ctrl or Shift, respectively, to select fields one by one or select a range of contiguous fields in the list.
6.	On the Criteria row of CustLastName, type >m for customers to retrieve records of customers whose last names begin with m through z. Access adds quotation marks around the m when you click in another row.
7.	Type a in the Sort row of CustLastName for an ascending sort. Deselect Show in this column.
8.	Choose File, Save or File Save As. Save your query as qryCustomersMZ.
9.	Click the View button on the far left of the toolbar (View) to see your records. You can run, or execute, a select query in several ways. In the Database window, you can open the query. Within the query itself, you can switch from Design to Datasheet view, either by clicking the View button or by choosing View, Datasheet View. You can also choose Query, Run or click the Run button (see Figure 8.2).
10.	Close the query.

NOTE

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.

"Drag-'Em-All" Method

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:

1.	Open the tblCustomers table.
2.	Select the Last Name field and click the Sort Ascending (AZ) button.
3.	In the State field, right-click any instance of CA and choose Filter By Selection.
4.	Choose Records, Filter, Advance Filter/Sort.
5.	Choose File, Save As Query.
6.	Name the query qryGoldenStateCustomers and click OK.
7.	Close the Advanced Filter window and close the table. Don't save your changes.
8.	In the Database window, click Queries and then open qryGoldenState Customers. All fields are displayed.
9.	Click View to switch to Design view. Although two columns on the grid have been used to specify a sort and criteria, the Show box is deselected in both. Seemingly, no fields should be displayed.
10.	Choose Properties on the View menu. The Output All Fields property is set to Yes. That's the reason all fields are displayed in the datasheet.
11.	Close the property sheet and close the query.

TIP

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.

Unique Values

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:

1.	Select the tblCustomers table in the Database window.
2.	Choose Insert, Query and, with Design View selected, click OK.
3.	From the field list, add the CustState field to the design grid.
4.	In the Sort row, type a for an ascending sort.
5.	Click View to see your records. You have the list of states, but the values are repeated.
6.	Click View to return to Design view. Right-click anywhere in the top pane outside the field list and choose Properties.
7.	In the Query property sheet, edit the Unique Values property to Yes. Close the property sheet.
8.	Click View to return to Datasheet view. Each state is now listed just once.
9.	Choose File, Save. Save the query as qryUniqueValues and close it.

Sorting

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.

Q&A

Q1:	I'm still not sure I understand how sorts work in queries. What happens if I choose an ascending sort for ProductName in the design grid and a descending sort for the same field in the datasheet? Which one takes precedence?
A1:	The one you saved in the datasheet takes precedence. If you're having problems figuring out what's going on with your sorts, here's what to do. In the Query Design window, right-click in any open area of the top pane and choose Properties. Scroll down to the Order By property and delete whatever's in there. Review your sorts on the design grid to make sure they're okay, and save your changes.