How to: Define Query Results

Article
07/09/2007

After you have opened the Query Designer and selected the table or view that contains the information you want, you can define your results. At a minimum, you need to select the fields you want. You can also define your results by setting the order the selected fields appear and setting filters to select which records appear.

Selecting Fields You Want

Before you can run a query, you must select some fields to include in the results. In some cases, you might want to use all the fields from the table or view. At other times, you might want to focus the query on a few selected fields, such as fields you want to include in a report.

Be sure to include any fields you might want to use to sort or group the results of your query. When you select the fields, you can also set the order they appear in your output.

Use the Fields tab in the lower pane of the Query Designer to select the fields you want to include in the results of your query.

To add a field to the query output

Select the name of the field and choose Add.

-or-
Drag the field name to the Selected fields box.

Selecting All Fields for Output

You can select all of the fields by name or use the asterisk wild card. If you select the fields by name, the exact names of the fields are included in the query. If you run the query after adding fields to the table, the new fields are not included in the results.

If you use the wild card, the asterisk is included in the query and includes all fields that are in the table at the time of the query. If the table structure has changed since the query was created, the new fields also appear in the query results.

To add all available fields to a query at once

Choose Add All to add fields by name.

-or-
Drag the asterisk at the top of the table into the Selected fields box.

Displaying an Alias for a Field

You can make your query results easier to read and understand by adding a descriptive caption to a results field. For example, you might want to display the word "SumMaxOrd" at the top of the results column instead of the field name or expression, SUM(MaxOrdAmount).

To add an alias for a field

In the Functions and expressions box, type the field name, and then type AS and the alias, as in this example:
```
SUM(maxorderamt) AS SumMaxOrd
```
Choose Add to place the field with the alias in the Selected fields box.

Setting the Order for Output Fields

The order that fields appear in the Fields tab determines the order of the columns of information in your query output.

To change the column order of the query output

Drag the mover box, located to the left of a field name, up or down.

If you want to change the order that rows of information will be sorted, use the Order By tab instead.

Selecting the Records You Want

Selecting the records you want to find is the key step that determines the results of your query. With the Filter tab in the Query Designer, you can formulate the WHERE clause of a select statement to tell Visual FoxPro which records to search for and retrieve.

You may want to find a specific subset of data to include in a report or other output: for example, all customers with outstanding balances, all customers in a specific region or postal code, and so on. To see only the records you want, enter a value or range of values to compare the records against.

In Visual FoxPro, you use the Filter tab to specify which field you want to use for selecting records, choose a comparison criterion, and enter an example of the value you want the field to be compared to.

To specify a filter

From the Field Name list, choose a field you want to use as the basis for selecting records.

Note

You can't use General or Memo fields in a filter.
From the Criteria list, choose the type of comparison to use.
Enter the comparison criterion in the Example box.
- Use quotation marks only if the string is the same as the name of a field in a table in the query; otherwise, do not enclose character strings in quotation marks.
- If you use dates, do not enclose them in braces.
- Place a period before and after a logical literal (.T.).
- If you enter a field name from a table in the query, Visual FoxPro will recognize it as a field.
If you want capitalization to be ignored in searches on character data, select the Case button.

If you want to reverse the meaning of a logical operator, select the Not button. For example, if you want to find customers in all regions except Washington, use the selection expression in the following example:

Customer.region Not Like WA

To further tune your search, you can add more filters in the Filter tab. For more information, see "Fine-Tuning Your Search" in How to: Customize Queries.

If you are using more than one table or view in your query, you can expand the records you select by the join type you choose.

Selecting a Number or Percentage of Records

If you only need a certain number or percentage of records from the result set that your query returns, you can use the Top options on the Miscellaneous tab in the Query Designer or View Designer, or you can add a TOP clause to your SQL SELECT statement. You can provide a number from 1 to 32,767 or a percentage from 0.01 to 99.99 in a TOP clause. Visual FoxPro sorts records first and then extracts the top number or percent of records.

To limit the number of records to retrieve

In the Query Designer, select the Miscellaneous tab.
In the Top area, clear the All box to make other options in the Top area available.
Choose one of the following:
- In the Number of records box, type or select the maximum number of records you want to retrieve.
-OR-
- Click Percent to change the Number of records box to Percentage so you can type or select the maximum percentage of records you want to retrieve.

For example, if you want to select the top 10 customers with the highest order amounts, you can specify a GROUP BY on CUST_ID to show one aggregate record for each customer and sort by ORDER_AMT in the ORDER BY clause. To get a true TOP 10, you need to specify a descending sort on the ORDER_AMT so that the records with the highest order amounts appear first in the results. If you use an ascending sort, the result records are ordered from the least order amount to the most. The top records you select from the result set would actually have the lowest values.

SELECT TOP 10 *;
FROM testdata!customer INNER JOIN testdata!orders ;
ON Customer.cust_id = Orders.cust_id;
GROUP BY Customer.cust_id;
ORDER BY Orders.order_amt DESC