Ms Office Tune Up

When you put together an Access query, you add a table, select one or more fields from that table, and then specify your criteria: the expression that tells Access which records you want to see. When you then run the query, Access displays a dynaset (that is, a datasheet with the query results) that includes every record in the table that matches your criteria.

However, there are times when you don’t need to see every record that satisfies your crite­ria. For example, in a query of orders for last month, you might be interested in seeing only the biggest orders, whether in terms of the most units or the most dollars. Similarly, in a query of products, you might want to view only the most expensive items or the ones with the least inventory in stock.

For these situations, you can tell Access to restrict the dynaset to a specified number of records or to a percentage of the total:

1. Set up your query table, fields, and criteria.
2. For the field in which you want to see the top values, apply a Descending sort. (If you want to see the bottom values, instead, apply an Ascending sort.)
3. In the Design tab, use the Top Values combo box (see Figure 1) to specify how much of the dynaset you want to return in the results:
   • Select a number or percentage value from the list.
   • Type a number or percentage in the text box.
   • Choose All to see every record.
4. Choose Design, Run to see the query results.

Figure 1 Use the Top Values combo box to tell Access the number or percent­age of the top (or bottom) values you want to return.

Technorati Tags: top values, query, access 2007

Popularity: 3% [?]