Microsoft Office Tutorials and References
In Depth Information
Six Kinds of Queries
Six Kinds of Queries
For your pleasure and entertainment, the rest of this chapter describes six
useful types of queries. Access offers a handful of other queries, but I won’t
go there. Those queries are pretty complicated. If you become adept at
querying, however, you’re invited to look into the Help system for advice about
running the query types that aren’t explained here.
A select query is the standard kind of query, which I explain earlier in this
chapter. A select query gathers information from one or more database tables
and displays the information in a datasheet. A select query is the most
common query, the primal query, the starting point for most other queries.
A top-value query is an easy way to find out, in a Number or Currency
field, the highest or lowest values. On the Query grid, enter the name of
the Number or Currency field you want to know more about; then choose
Ascending in the Sort drop-down list to rank values from lowest to highest or
Descending in the Sort drop-down list to rank values from highest to lowest.
Finally, on the (Query Tools) Design tab, enter a value in the Return text box
or choose a value on the Return drop-down list:
✦ Highest or lowest by percentage: Enter or choose a percentage to find,
for example, the highest or lowest 25 percent of the values. To enter a
percentage, type a percent sign (%) after your entry and press the Enter key.
✦ Highest or lowest by ranking number: Enter or choose a number to
find, for example, the top-ten or lowest-ten values. Press the Enter key
after you enter a number.
This may seem counterintuitive, but to see the top values, you have to sort the
field you’re ranking in descending order. For example, if you sort employees by
number of sales in descending order, the employees with the top sales appear
at the top. To see the bottom values, sort in ascending order.
Similar to a top-value query, a summary query is a way of getting cumulative
information about all the data in a field. In a field that stores data about sales
in Kentucky, for example, you can find the average amount of each sale, the
total amount of all the sales, the total number of all the sales, and other data.
To run a summary query, go to the (Query Tools) Design tab and click the
Totals button. A new row called Total appears on the Query grid. Open the
Total drop-down list in the field whose contents you want to summarize and
choose a function. Table 4-2 describes the functions.