ID: Q114720
2.60 | 2.60
The information in this article applies to:
The information below describes the Updatable Query Wizard and how to use it to create an updatable query.
This information is also found in FoxPro online Help in the "Updatable Query Wizard" topic.
The Updatable Query Wizard searches one or more tables for records that match the criteria you specify. It creates an updatable query file with an .FPQ extension. When you run your query, you create a set of data which you can edit; changes you make to the records in the query results will be reflected in the tables the query is based upon.
The Updatable Query Wizard has five main steps:
1. Selecting and relating tables
2. Selecting fields and adding expressions
3. Setting the sort order of records (optional)
4. Limiting or filtering the records retrieved (optional)
5. Viewing, saving, and browsing the query, or creating an AutoReport
from it
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 Wizard from the Catalog Manager, you can also choose the Open Table... button to use a table that is not currently open.
When you have more than one table selected, the wizard displays this screen so you can relate the tables. Select matching fields from the parent table and the child table, then choose Add.
For example, if you are setting a relationship 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 relating tables, choose Next to continue to the next step.
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:
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.
NOTE: You cannot use aggregate functions (such as AVG, COUNT, MAX, MIN, or SUM) in your query.
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.
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.
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. Only fields from the parent table are available for sorting.
For example, if you choose Customer.Lastname and Customer.Ino, the records will be sorted by customer name and then by customer invoice number.
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.
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
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
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
STATE = WA
OR
STATE = CA
(STATE=WA
OR
STATE=CA)
AND
ORDER > $100
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.
the Preview button.
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.
Keywords : FxtoolRqbe
Version : 2.60 | 2.60
Platform : MS-DOS WINDOWSLast Reviewed: May 1, 1996