Home > Sample chapters > Microsoft Office > Access

Creating Queries in Access 2010

Finding the Largest and Smallest Values in a Field by Using a Query

When you create a query, you can have Access display a set number of the highest and lowest values in the query’s results. You can also have Access display rows that contain the top or bottom values by asking Access to display a certain percentage of rows in the query’s results. For example, if you reward the top 10 percent of your sales staff, you can create a query that sorts the query results according to the values in the Total Sales field and displays the best values. When you filter a query’s results by a percentage, you don’t need to know the exact number of records in your table. In other words, if your sales staff is made up of 40 employees, creating a query that displays the top 10 percent of sales totals will return the top 4 representatives.

Find the Largest Values in a Field

  1. Open a query in Design view.

  2. Click the Sort cell of the field in which you want to find the top values.

  3. Click the down arrow that appears.

  4. Click Descending.

  5. If necessary, click the Design tab.

  6. Using the Top Values field control, perform any of these tasks:

    • Click the control’s down arrow and select a default value to specify the number of values or the percentage of values to display.

    • Type a number indicating the number of values you want displayed in the query results.

    • Type a percentage indicating the portion of the table’s rows you want displayed in the query results.

  7. Click Run.

    httpatomoreillycomsourcemspimages613776.jpg

Find the Smallest Values in a Field

  1. Open a query in Design view.

  2. Click the Sort cell of the field in which you want to find the top values.

  3. Click the down arrow that appears.

  4. Click Ascending.

  5. If necessary, click the Design tab.

  6. Using the Top Values field control, perform any of these tasks:

    • Click the control’s down arrow and select a default value to specify the number of values or the percentage of values to display.

    • Type a number indicating the number of values you want displayed in the query results.

    • Type a percentage indicating the portion of the table’s rows you want displayed in the query results.

  7. Click Run.

    httpatomoreillycomsourcemspimages613778.jpg