Figure 7.6
The SQL Design view for the Current Product List query.
Looking at the Basic Structure of Queries
The previous section showed you how to use the two different query design view windows. In this section,
you’ll learn how a query is constructed. This section concentrates on the QBE Design Grid View window.
You’ll learn all about fields, how the sorting is specified, how the record criteria is determined, and how to
view the properties for the query and its fields. You’ll be using the Current Product List query shown in
Figure 7.5 as the sample query demonstrating these features.
Fields
Every select query returns at least one field. The fields that appear in the grid at the bottom of the Design Grid
window are the fields that will be displayed when the query is executed or that will be used in the sorting or
criteria settings for the query. (Refer to Figure 7.5.)
Action queries do not return fields, but their fields are shown in the Design Grid. The fields that appear in an
action query’s Design Grid are the fields that are being acted upon by the query or that are used in the sorting
or criteria settings for the query.
An action query modifies many records in a single execution. Action queries are used to update,
insert, and delete records, or to populate a new table from data in existing tables.
In the Design Grid, the Show check box determines whether the field is displayed in the query’s datasheet. In
the Current Product List query, the
Discontinued
field is not shown in the Datasheet view; only the
ProductID
and
ProductName
fields appear.
Sorting
Let’s say you wanted to present the results of a query in a specific order. The Current Product List query, for
example, is sorted by the
ProductName
field. Access 2000 provides a Sort row in the Design Grid view that
you use to specify the sorting for a query. The information in the Sort row determines the order in which
records appear in the datasheet or in any reports or forms that use the query as their source of data.
You can set the sort order to Ascending (from A to Z, for example), Descending (from Z to A), or Not Sorted.
You also can use the sort order on numeric data returned by the query. If a Numeric field is sorted in
ascending order, the smallest values appear first. In descending order, the largest numbers appear first.
Criteria
Each field that appears in the Design Grid can be used to determine which records are returned by the query.
Only records with data matching the specified criteria are returned by the query when it’s executed or used in
a form or report.
For the Current Product List query, the third column specifies that the query should return only rows from the
Product List table that have their
Discontinued
field set to
No
.
For each field, you can specify criteria by using the rows below the first criteria row. These extra criteria
values are used in an OR fashion with the other criteria specified for this field. This means that records are
returned when the values in the field match any of the rows in the Criteria area.
If the Criteria rows are used for multiple fields, the criteria information is combined in an
AND
fashion. This
means that the data must meet all the criteria entries in order to be included in the resulting set of data. For
example, you might want to view the Current Product List but only see the products which are in stock. In this
case, you’d add the
UnitsInStock
field to the query and enter
>0
in the Criteria row (see Figure 7.7).
|
|