Creating Queries in Access 2010

6/15/2010

Contents

Introducing Query Types
Creating a Query Using the Query Wizard
Editing a Query in Design View
Using Criteria to Focus Query Results
Introducing Operators
Using Queries to Calculate Values
Creating a Parameter Query
Finding Duplicate Records
Finding Unmatched Records
Writing Query Results to a New Table
Creating an Update Query
Creating a Crosstab Query
Finding the Largest and Smallest Values in a Field by Using a Query

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

Open a query in Design view.
Click the Sort cell of the field in which you want to find the top values.
Click the down arrow that appears.
Click Descending.
If necessary, click the Design tab.
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.
Click Run.

Find the Smallest Values in a Field

Open a query in Design view.
Click the Sort cell of the field in which you want to find the top values.
Click the down arrow that appears.
Click Ascending.
If necessary, click the Design tab.
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.
Click Run.