How to: Customize Queries

  • Article

You can customize your queries further in the Query Designer. You can use filters to expand or narrow the results of your query. You can also add expressions to perform calculations or functions on the data in your fields.

Filtering Records in Queries

You can use filter expressions to narrow or expand the range of records that your query retrieves. For example, suppose you want to retrieve records that meet more than one criterion, for example, you want to retrieve customers from a given state who have outstanding balances greater than $1,000. In this scenario, you want to narrow the range of records your query retrieves. In contrast, suppose you want to retrieve records that meet any criterion, for example, customers from Washington or from California. In this scenario, you want to narrow the range of records your query retrieves.

When you want to create filter expressions that include more than one criterion, use the logical AND operator. When you want to create filter expressions that use any criterion you specify, use the logical OR operator.

You can also combine AND and OR conditions to select specific sets of records. For example, you might want to retrieve records for customers from Washington or California who have maximum order amounts larger than $5,000.

To specify AND or OR conditions for filter expressions

  1. Open the query in the Query Designer.

  2. Click the Filter tab, and select the filter expression you want.

  3. In the Logical column, click AND or OR.

For more information, see Filter Tab, Query and View Designers.

Eliminating Duplicate Records in Queries

You can remove duplicate records from queries. Duplicate records contain fields where all the values in the fields match each other.

To remove duplicate records from queries

  1. Open the query in the Query Designer.

  2. Click the Miscellaneous tab, and then No duplicates.

    Note

    Selecting No duplicates inserts the DISTINCT keyword preceding the fields in the SQL SELECT statement of the query. For more information, see SELECT - SQL Command.

Retrieving the Top Number or Percentage of Records

You can specify how many or what percentage of records with the highest or lowest values in a particular field you want the query to return. For example, the query can display those records with the 10 highest or lowest values in a specific field or the records with the highest or lowest 10 percent of values in the field.

Using the Top setting on the Miscellaneous tab, you can either set a number or a percentage of records you want to see. To set whether you are choosing the top or bottom, you set the sort order for your query to either descending to see the top or ascending to see the bottom.

To retrieve a number or percentage of top records

  1. In the Order By tab, select the field you want to retrieve top values for, then choose Descending to display the highest values or Ascending to display the lowest values. If you are sorting on additional fields, place them after the top values field in the order by list.

  2. In the Miscellaneous tab, type the number for the percentage or the number of highest or lowest values you want retrieved in the Number of records box. To display a percentage, choose Percent.

  3. If you do not want duplicate records included in the number or percentage, choose No duplicates.

Adding Expressions to Queries

You can create more flexible and powerful queries if you incorporate expressions, either in a filter or as result fields. You can include functions and expressions in the output of your query, using the box at the bottom of the Fields tab. If you want to name the field holding the expression, you can add an alias.

For example, you might want your query results to include the sum of all order amounts with the alias Total:

SUM(orders.order_amt) AS Total

You can type an expression directly into the box or use the Expression Builder on the Fields tab.

To add an expression to your query output

  1. In the Fields tab, type the expression in the Functions and Expressions box.

    -or-

    Choose the dialog button to use the Expression Builder and enter an expression in the Functions and expressions box.

  2. Choose the Add button to place the expression in the Selected fields box.

    Note

    Null values are ignored in calculations. For more information on null values in expressions, search for "Null Values".

Instead of searching for records that match one or more fields, you can use an expression to combine two fields, or perform a calculation based on a field and search for records that match the combined or calculated field.

You can type expressions directly into the Example box. If you want some help, you can use the Expression Builder, which is available from the dialog button next to the Expressions and Functions box in the Fields tab.

For example, using the Orders table, you might want to check total discounts for a customer by combining order amount and order discount in an expression like the following:

Orders.order_amt * Orders.order_dsc

See Also

Tasks

How to: Create Queries (Visual FoxPro)

How to: Customize Queries Using SQL SELECT Statements

How to: Run Queries (Visual FoxPro)

How to: Organize Query Results

How to: Query Multiple Tables and Views

Reference

Expression Builder Dialog Box

Fields Tab, Query and View Designers

Other Resources

Working with Queries