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 ...