Customizing Queries

Article
11/14/2006

Other options available with the Query Designer make it easy to further customize your queries. You can use filters to expand or narrow your search. You can also add expressions to perform calculations or functions on the data in your fields.

Fine-Tuning Your Search

You might find that you want more control over the results returned by a query. For example, you might want to find records that meet more than one criterion, such as customers from a given state who have outstanding balances greater than $1,000. Or, you might need to retrieve records that meet either one criterion or another: customers from Washington or from California. In either case, you need to add more statements to the Filter tab.

In Visual FoxPro, filter statements are automatically combined with a logical AND when you enter them in successive rows in the Filter tab. If you want to retrieve records that meet two or more alternative conditions, you can insert a logical OR operator between rows with the Add Or button.

Narrowing Your Search

If you want your query to retrieve only records that meet more than one criterion, list the criteria on separate lines in the Filter tab. Criteria in a series are combined with an implicit AND statement, so that only records that meet all the criteria listed will be retrieved.

For example, suppose you are searching an employee table, and you want to see all the Sales Representatives in the United Kingdom. You would enter the two conditions you are searching for on separate lines.

To set AND conditions

Enter filters in the Filter tab, and choose AND in the Logical column.

Expanding Your Search

If you want your query to retrieve records that meet any of a series of filters, combine filters by inserting OR conditions between them.

For example, you might want to search for customers who live in Washington or California.

To set OR conditions

Select a filter, and then choose OR in the Logical column.

Combining Conditions

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.

Eliminating Duplicate Records from a Query

Duplicate records are records in which every field of the result matches. If you want to remove duplicate records from the query results, select the No Duplicates box on the Miscellaneous tab. Otherwise, make sure the No Duplicates box is cleared.

Note If No Duplicates is selected, the word DISTINCT precedes the fields in the SELECT portion of the SELECT command.

Retrieving a Number or Percentage of Top 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

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.
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.
If you do not want duplicate records included in the number or percentage, choose No duplicates.

Adding Expressions to a Query

You can create more flexible and powerful queries if you incorporate expressions, either in a filter or as result fields.

Adding Expressions to Results

You can also 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

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.
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".

Filtering with Expressions

Instead of simply 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