Microsoft Office Tutorials and References
In Depth Information
Editing a Query
Note that using a percentage doesn’t show values that fall in the top x
percent; it shows you the top x percent of the values. Suppose that you’re
looking at the test scores of 20 students. The test scores fall between 0 and 100
but are mostly in the 80s and 90s. If you ask to see the top 20 percent,
Access shows you the top 4 scores (20 percent of 20 records) — not the
scores that are 80 or higher. To see the scores that are 80 or higher, type the
criterion >=80 in the Test Score column of the design grid.
To display the top values found by a query, follow these steps:
1. Create your query with all the fields and criteria that you need.
2. Choose the field you want to sort by.
3. Set the field’s Sort row to ascending or descending order.
Access uses this setting to figure out which top values you’re looking
for. If you sort products by using the Selling Price field and sort in
ascending order, the cheapest products are at the top of the datasheet.
When you ask for the top five prices, you get the five cheapest products.
To get the most expensive products, sort in descending order so that
the most expensive products appear at the top of the datasheet.
4. Change the Return (Top Values) option by typing a value or a value
followed by a percent sign.
You can also choose a value from the drop-down menu. To see the top
three values, type . To see the top 3 percent of the values, type .
5. Click the View button to see only the top values in the datasheet.
You can use fields to sort data — or use criteria for the fields to filter data —
without having to display the field in the query datasheet. Deselect the Show
check box in the design grid when you don’t want to display the column in
the datasheet. The next time you open the query in Design view, you find
that Access has moved the hidden field(s) to the right side of the grid. If
the field is hidden and not used for sort order or criteria, Access removes it
from the grid.
Changing the format of a query field
The format of fields displayed in a query is determined by the field’s
properties in its native table. If the field is defined as having a currency format in its
table, that’s what you see in the query. Note, however, that you can change
the format of any field for the query.
To change the format of a field, follow these steps: