Access Cookbook - Ken Getz [10]
In addition, we'd like to thank Helen Feddema, Mike Gunderloy, and Dan Haught for their contributions to the first edition of this book, some of which remain (though altered for Access 2002). This book also went through a second edition, for Access 95, but was never revised for Access 97 or Access 2000. We appreciate the support of John Osborn and the editorial team at O'Reilly and Associates (including our editor, David Clark) for having the faith in the book, and in Access, to allow us to revise and publish this edition. We would also like to thank those fervent readers who sent many, many emails asking about Access 97 and Access 2000 versions of the book, which were never published. You know who you are, and we hope this revision satisfies your requests!
Special thanks also go to those who contributed suggestions and read chapters in their early stages, including Joe Maki, Sue Hoegemeier, and Jim Newman.
We also wish to acknowledge all the hard-working people at Microsoft who've given us these great products. In particular, Bill Ramos, Tim Getsch, Christina Storm, and Rita Nikas were very helpful to us as we prepared this latest edition.
Jan Fransen did a terrific job creating the chapter covering data access pages—we're very grateful to Jan for this important contribution.
We'd like to thank Michael Kaplan, a technical editor on the Access 95 edition of this book, who reviewed every word and every byte on the CD for that edition with loving care. The success of this book will be, in part, due to Michael's diligence.
Finally, the authors would like to acknowledge the constant support of their families and loved ones, especially Peter, Suzanne, and Mary.
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.