Progress
Results User’s Guide
for Windows
Changing Table Relationships in Multi-table Queries
When you use a multi-table query, Results uses table relationships to determine the set of records it returns. Results contains two types of table relationships, partial and complete.
When you create a query, Results automatically establishes a partial relationship between each pair of tables in the query. A partial relationship, also called an inner join, accesses only those records in the first table that have related records in the second table. If you want to access all the records in the first table whether or not they have related records in the second table, change the relationship to a complete relationship. A complete relationship is also called an outer join. See "Record Displays in Multi-table Queries" in "Query Basics," for examples of partial and complete relationships.If you set a complete relationship between a pair of tables and use sort ordering to control the order in which the query retrieves records, Results automatically uses the primary index in the first table in the relationship as the first sort field. (For more information, see the "Sorting Records" section. For example, you might have a query that displays the invoices for your customers by invoice date, as shown in the following example:
If you change the table relationship for this query to a complete relationship, Results will automatically use the Cust-Num field (primary index in the first table) then the Invoice-Date field to sort the query. Thus the query will display the records by invoice date for each customer.
Note that the values for aggregate subtotals and totals only associated with fields from the second table will change to reflect the new sort order when you change the table relationship to a complete relationship. For example, if you created an aggregate total to total the amount of the invoices by date, then changed the relationship between the Customer and Invoice tables to a complete relationship, the aggregate total would then total the amount of the invoices by date for each customer.
Follow these steps to change the relationship between one or more pair of tables:
- Choose Table
Relationship Types. The Relationship Types dialog box appears:
Partial relationships are displayed in white and complete relationships are displayed in reverse video.
- To change the type of relationship between a pair of tables, simply select the pair of tables you want to modify. Results automatically toggles to the opposite relationship type.
- When you are done modifying relationship types, choose OK.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |