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.