Access Cookbook

Chapter 1. Queries

Access queries—the six types that can be created on the easy-to-use query by example (QBE) grid, plus the three SQL-specific queries—give you a tremendous amount of power and flexibility in selecting, sorting, summarizing, modifying, and formatting the data stored in your tables before presenting it to the user on forms or printing it on reports. Access queries can be intimidating at first, but mastering queries will give you complete control over the appearance and functionality of your forms and reports. And Access queries are flexible—once you learn how to control them, you can use them in places where you might have written less efficient program code.

In this chapter you’ll learn to create parameter queries, which allow you to control selected rows of a report at runtime rather than at design time. You’ll use this same technique to control the available values in one combo box based on the choice in another. You’ll study the ways to control the output of crosstab queries and will learn a handy technique for mailing labels that lets you group labels by residence to avoid sending duplicate mailings to family members. You’ll learn to take advantage of update queries to alter the values in one table based on the values from another, and you’ll learn a trick that can be used to filter a query based on the value of a Visual Basic for Applications (VBA) variable. In case you need to pull random sets of data from a data source, you’ll see how to use a query to create a random set of rows. And you’ll examine a query that uses a Partition function to perform an aging analysis.

You’ll also find solutions dealing with more advanced uses of queries. You’ll learn how to create a join that’s based on a non-equality comparison, how to use union queries to horizontally splice together the data from two tables, and how to take advantage of union queries to add an extra choice to a combo box. You’ll find out how to create self-join queries to model powerful recursive relationships, how to perform case-sensitive searches using a query, and how to use data definition language (DDL) queries to create or alter the structure of a table. You’ll also examine a suggested method for storing query information in a table, which can be protected and made invisible in applications, giving you complete control over which queries are run and when. Finally, you’ll learn a technique for creating recordsets in VBA code based on parameter queries.

Many of the examples in this chapter are based on a fictional music collection database that you could use to keep track of your favorite musicians and your album collection.

Specify Query Criteria at Runtime

Problem

When you design a query, you don’t always know which subset of records you would like to see when you run the query. Instead of creating several queries with the same basic design but slightly different criteria, you’d like to be able to create one query that can be used to return the same fields, but a different set of records, each time it’s run.

Solution

Access lets you create a query with one or more replaceable parameters that it will request at runtime (when you run the query). This solution demonstrates how you can create and run parameter queries using the default parameter prompt.

Here are the steps to create a parameter query using default prompts:

1. Create any type of query in query design view.

2. Choose a field for which you wish to define a parameter. Create a parameter for that field by entering the prompt you would like to see when the query is executed surrounded by square brackets ([]) in the Criteria row for that field. For the example query qryAlbumsPrm1, you would create a parameter for the MusicType field by typing:

   [Type of Music?]

   in the Criteria row under MusicType.

3. Select Parameters from the Query menu to open the Query Parameters dialog, where you declare the parameter. For this example, enter:

   Type of Music?

   in the Parameter column of the Query Parameters dialog, and choose:

   Text

   from the data type combo box to tell Access that this is a text parameter. This step is optional in this query, but some queries require it (see Section 1.1.3), so make it a habit. Steps 2 and 3 are shown in Figure 1-1.

   

   Figure 1-1. The qryAlbumsPrm1 parameter query in design view

4. Save the query and run it. Access will prompt you to enter the type of music with a parameter dialog (see Figure 1-2).

   

   Figure 1-2. The Enter Parameter Value dialog for qryAlbumsPrm1

   To see how this works using the sample database, open 01-01.MDB and run the qryAlbumsPrm1 query. You will be prompted for the type of music. Enter a music type, such as rock, alternative rock, or jazz. The query will then execute, returning only the records of the specified music type. For example, if you enter “Alternative Rock” at the prompt, you’ll see the datasheet shown in Figure 1-3.

   

   Figure 1-3. The datasheet for qryAlbumsPrm1

Discussion

For queries with simple text parameters, you can get away without declaring the parameter using the Query → Parameters command. If you create parameters for crosstab or action queries, however, you must declare the parameter. We recommend that you get in the habit of always declaring all parameters to eliminate any chance of ambiguity. The entries you make in the Parameters dialog end up in the Parameters clause that is added to the beginning of the query’s SQL, which you can see by selecting View → SQL View.

The result of a parameter query needn’t be a query’s datasheet. You can base reports, forms, and even other queries on a parameter query. When you run the object that is based on the parameter query—for example, a report—Access knows enough to resolve the parameters prior to running the report.

You can use parameters in any type of query, including select, totals, crosstab, action, and union queries.