The Nature of Queries

I suppose it's frivolous to portray one Access object as more pleasant and agreeable to create than the others. You build objects because you need them, not to fill idle hours.

Still, compared with building other Access objects, it's hard not to have some special affection for the query. Tables require toying with those ornery primary keys, as well as pondering settings for long lists of field properties. Forms and reports of any complexity offer a Hobson's choice of tedium through control-by-control construction, or disappointment from cookie-cutter wizards.

In contrast, the query is a veritable playground. The unadorned design grid is simple and entirely straightforward. Fields can be added and deleted with ease. Criteria can quickly be edited again and again to retrieve different record sets.

NOTE

You can use parameter queries to quickly apply different criteria in the same query. They eliminate the need to create a new query for each set of criteria. Chapter 9, "Queries, Part II," discusses parameter queries.

Not that queries won't cause you any problems. If you stick to simple queries that can be created with a few strategically placed entries on the design grid, you should have few worries. But building queries with expressions and functions of some sophistication is a different matter: A single misplaced bracket can cause much misery. All in all, though, queries are the most flexible, powerful, and enjoyable objects with which to work.

Queries Versus Tables

One quality of queries that must be emphasized is that they are dynamic. Because the datasheet of a table and a query look alike, it's easy to think that the objects themselves are fundamentally the same. But they are quite different. When you create and save a query, all you are saving is the query's structurethe fields, sorts, criteria, and so on. The records themselves are stored in tables. Each time you run a query, the result is re-created with the current records from the underlying table(s). This dynamism is an essential characteristic of a query's nature.

Queries Versus Filters

The similarities between filters and queries will be readily apparent, so it's useful at this point to discuss how they differ. Queries are permanent objects in your database with their own icon in the Database window. Filters are temporary snapshots of specific records in a table, form, or query.

Longevity might not be their most distinguishing feature, however. Queries can easily be deleted from your database, whereas a filter can be saved in a datasheet or form and be quickly reapplied upon opening. Thus, the seemingly short-term filter can actually outlive the supposedly eternal query.

The most profound distinction lies in their use. You'll usually create a filter to find and view records with specific criteria in a form or table and to solve the task of the moment. You'll use queries in many different ways, most notably as the record source for forms and reports.

Query Design Versus Advanced Filter

Further evidence of how filters and queries differ can be seen from a comparison of the Query Design and Advanced Filter windows. At first glance, they appear similar, with field lists at the top and the design grid below (see Figure 8.1). In both, you create sorts in the Sort row and designate criteria in the Criteria and Or rows.

Figure 8.1. The Query Design window is on the left, and the Advanced Filter window is on the right. In the query, the CustFirstName, CustLastName, and CustZIP fields have been added to the grid to display them. The CustZIP column is also used to designate criteria. Unlike a query, in which you must include a field on the design grid to display it, on the Advanced Filter grid, you need to include only the fields you want to filter.

Important differences emerge in their capacities and functions, however. As you saw in Chapter 7, "Find and Filter," when you filter a datasheet or form, all fields are automatically included in the record set. You use the design grid only to set sorts and designate criteria within that specific object. In a query, you use the design grid to indicate the specific fields you want included. You can add individual fields or entire field lists to the grid from any of the tables or queries in your database.

Thus, in a query there's an additional row called Show. It has a check box that indicates whether the field will be displayed in the results (see Figure 8.1). As you'll see presently, you deselect the box when you want to use a column just to set criteria. In Chapter 9, you'll see that the query design grid has the capability to display additional rows as needed for creating total, crosstab, and action queries.

TIP

For a better look at the contents of the Query Design window, drag the bottom pane down a couple of inches. That will give you plenty of room to display lengthy field lists in the top pane, as Figure 8.1 demonstrates.