Microsoft KB Archive/114585

{| = Overview of FoxPro for Windows Client-Server Query Wizard =
 * width="100%"|

ID: Q114585

2.60 WINDOWS

kbtool kbprg The information in this article applies to:
 * Microsoft FoxPro for Windows, version 2.6

SUMMARY
The information below describes the Client-Server Query Wizard.

This information is also found in FoxPro online Help in the "Client-Server Query Wizard" topic.

MORE INFORMATION
The Client-Server Query Wizard lets you connect to any ODBC data server and use a SQL query to retrieve data from it. It creates a query file with a .CSQ extension. Each time you run the query file, it will initiate a connection with the server and run the query.

FoxPro .CSQ queries allow you to use aggregate functions (such as AVG, COUNT, MAX, MIN, or SUM) in your query where the ODBC source allows that capability. The resulting query is read-only; you can browse it or incorporate it into a report, but you cannot update the records.

NOTE: The Client-Server Query Wizard is available only if you are using the Professional edition of Microsoft FoxPro version 2.6. Some back-end database servers do not support all the functionality available through the wizard, such as outer joins. Be sure to check to see if your ODBC driver supports the functions you request from the wizard. For information on troubleshooting connectivity issues, see Connectivity Kit Error Messages.

Before using the Client-Server Query Wizard, you need to:


 * Install the Microsoft FoxPro Connectivity Kit.
 * Install ODBC.
 * Register data sources or servers with ODBC.

For more information, see the FoxPro Connectivity Kit and ODBC documentation.

Client-Server Query Wizard Steps
The Client-Server Query Wizard has seven main steps:

1. Selecting and connecting to a server or data source

2. Selecting and joining tables

3. Selecting fields and adding expressions

4. Setting options to group query results

5. Setting the sort order of records (optional)

6. Limiting or filtering the records retrieved (optional)

7. Viewing, saving, or browsing the query, or creating an AutoReport

from it Detailed instructions for each step are provided below.

Step 1 of 7: Selecting a Server and Connecting to It
In this step, you log onto the server or data source you want to connect to when running your query. Check with your system administrator if you need help with server names or your user identifier and password.

NOTE: If you enter a password on this screen, the resulting query will be password-protected; that is, you will need to supply this password every time you run the query.

Data Source: From the list, select the name of the server or data source you want to use. The list contains the names of all registered data sources from the ODBC.INI file.

User Identifier: Enter your user identifier or user name.

Password: Enter your password, if required, for this server.

Database: (This name is optional) If there is more than one database on the server, enter the name of the database you want to use.

Step 2 of 7: Selecting and Joining Tables
First select the table or tables you want to use from the Available Tables list.

NOTE: If you are using the Query Wizard from the Catalog Manager, the tables on which you want to base your query must reside in the open catalog. If there are no tables found in the catalog and you click the Query Wizard button, you will be prompted to name your query and select a table on which to base the query. Next, RQBE will start from which you can create a query. If you are not using the Query Wizard from the Catalog Manager, you can also choose the Open Table... button to use a table that is not currently open.

Step 2a of 7: Joining Tables
When you select more than one table, the wizard displays this screen so you can join the tables. Select matching fields from the parent table and the child table, then choose Add.

For example, if you are setting a join between the Customer table and the Invoices table, you could set Customer.cno equal to Invoices.cno.

The Outer Join check box affects which records will be included in the output of your query. When the box is checked, all the parent table records are included, even if they do not have a matching record in the child table. When the box is not checked, your query will only contain records from the parent table if matches are found in the child table.

If you want to delete the relationship between two tables, select the relationship and choose the Remove button.

When you are done joining tables, choose Next to continue to the next step.

Step 3 of 7: Selecting Fields and Adding Expressions
Select the fields you want to include in the query. The list of available fields includes fields from all the tables you have selected.

To Select Fields, You Can:


 * Double-click each field name in the Available Fields list.
 * Select each field name and choose the Add button.
 * Choose the Add All button to use all the table fields.

If you add a field you don't need, select it and choose the Remove button. Adding Expressions to Your Query:

You can also add expressions to your query. An expression is a combination of operators, functions, and field names that evaluate to a single value. For example, you might want your query to include the sum of all prices:

SUM(cost) When you choose the Expression... button, the wizard displays the Expression dialog, where you can create the expression to add to your query. To Create an Expression with the Expression Dialog:

1. Enter a name for your expression in the Name of calculated field box.

2. Either type the expression directly into the Expression box, or select

String, Math, or Date expressions and combine them with the Fields available. 3. When you've finished building the expression, choose OK to confirm it. If you need to modify an expression after you have created it, select it in the Selected Fields list, and choose the Expression... button again.

Step 4 of 7: Grouping the Query Results
From the Available Fields list, select up to three fields by which to group the records in your query results.

For example, if you choose to group by state and zip code, the records will be sorted into groups by state, then grouped and sorted by zip code within each state.

This type of grouping can be useful in conjunction with any expressions you incorporate into your query.

For example, you could include an expression which does a SUM of all sales, then group the results by State, to find the total sales by state.

Step 5 of 7: Setting the Sort Order for the Records
In this screen, you determine how the records retrieved by the query will be sorted. Select up to three fields from the Available Fields list to set the sort order of the records. You can sort on fields from any of the selected tables.

For example, if you choose Customer.Name and Invoice.Ino, the records will be sorted by customer name, from the Customer table, and then by invoice number from the Invoices table.

Choose the Ascending option to sort records from the beginning of the alphabet, the lowest number, or the earliest date; choose the Descending option to sort records from the end of the alphabet, the highest number, or the latest date.

Step 6 of 7: Limiting the Contents of the Query
In this screen you can add an optional expression to further limit the records retrieved by the query. The effect is like filtering the results so that only records meeting certain criteria are included in the query results. For example, you could limit the search to last names starting with J by using the expression:

Customer.lastname BeginsWith J First select a field from the Fields list and an operator from the Operator list. Then enter a comparison value in the Value field, and choose the Add button to add the new expression to the expression box. Entering Values:

In the Value field, type the value you want the query to compare the field against. For example, if you are looking for customer records with a particular last name, type that name in the Value box, after choosing the last name field and the equal-sign operator. The resulting query expression would look like this:

LAST_NAME = JOHNSON Combining Expressions: You can create more complex queries by adding more expressions.

When you combine expressions, they are combined with an AND operator by default. For example:

STATE = WA  AND ORDER > $100 If you want the query to find records that match either expression, but not both, choose the OR button to add an OR operator between expressions. For example: STATE = WA  OR   STATE = CA You can add parentheses around an expression to make sure the expressions are evaluated as a unit. For example: (STATE=WA  OR   STATE=CA) AND ORDER > $100 To Create an Expression: 1. Select a field from the Fields list.

2. Select an operator from the Operator list.

3. Enter one or more values for comparison in the Value box.

4. Choose the Add button to add the resulting expression line to the

expression box. 5. To check the results of the filter expression on the query, choose the Preview button. To Remove an Expression:
 * Select the expression line and choose the Remove button.

Step 7 of 7: Previewing, Saving, and Browsing Your Query
Choose the Preview... button to see the results of your query in a Browse window. If you are satisfied with the results, choose one of the following options, and choose the Finish button.


 * Save Query for Later Use: Creates a Client-Server query file (.CSQ extension) which you can run later.
 * Save and Run Query: Saves the query and displays the records in a Browse window.
 * Save and Create an AutoReport: Creates and runs an automatic report based on the fields in the query.

Additional reference words: FoxWin 2.60 foxhelp.dbf foxhelp.hlp client/server structured query language KBCategory: kbtool kbprg KBSubcategory: FxtoolCk Keywords         : kbnetwork FxtoolCk Version          : 2.60 Platform         : WINDOWS
 * }