Database Reference
In-Depth Information
PRACTICE FILES
To complete the exercises in this chapter, you need the practice file
contained in the Chapter07 practice file folder. For more information, see “Download
the practice iles” in this topic's Introduction.
Creating queries by using a wizard
In Chapter 4 “Display data,” you learned how to retrieve information from a database table
by filtering it. These techniques are effective, but limited in the following ways:
▪
The filters are not saved, or are saved only temporarily.
▪
The filters are applied only to the table or form that is currently open.
If you want a filter to be permanently available, or if you want to filter more than one table
or tables that are not open, you need to move beyond filters and into the realm of queries.
The most common type of query is the select query. The easiest way to set up a select
query, especially when you are first learning about them, is to use a query wizard. Four
wizards are available:
▪
Simple
Sets up a query to retrieve data from one or more tables and displays the
results in a datasheet. For example, you could use a simple query to extract the name
and address of every customer who has ever placed an order.
▪
Find Duplicates
Sets up a query to locate records that have the same informa-
tion in one or more fields that you specify. For example, you could use this type
of query to extract the name and address of every customer who has placed more
than one order.
▪
Find Unmatched
Sets up a query to locate records in one table that don't have
related records in another table. For example, you could use this type of select query
to locate people in the Customer table who have never placed an order.
▪
Crosstab
Sets up a query to calculate and restructure data for easier analysis. You can
use a crosstab query to calculate a sum, average, count, or other type of total for data
that is grouped by two types of information, one down the left side of the datasheet
and one across the top. The cell at the junction of each row and column displays the
results of the query's calculation.
