لطفا منتظر باشید ...
Chapter 1. Queries
Access queriesthe six types that can
be created on the easy-to-use query by
example (QBE) grid, plus the three SQL-specific
queriesgive 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 flexibleonce 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.
