Displaying Data from a Related Form

See DQL 8 for more information on the sorting and grouping operators.

The query so far reads:

 for MEMBERS

 with TOTALDUE > 90 ;

  list records

   LASTNAME in order ;

Relational Operators

We want DataEase to list the date of each member's most recent reservation. However, this information does not appear in the Columns list because reservation dates are not stored in the MEMBERS table. Instead, the DATE field is stored in the RESERVATIONS table.

Because a pre-defined relationship exists between the RESERVATIONS table and the Primary table (MEMBERS), we can use a relational operator to access any information we need from the RESERVATIONS table. DQL provides seven relational operators (any, all, count of, highest of, lowest of, mean of, and sum of) that let you retrieve information from a table related to the Primary table.

To tell DataEase to find the most recently dated reservation for each member, enter the highest of relational statistical operator into the script before specifying the column name. Press Enter to start a new line, then double-click highest of in the Operators pick list or type it directly into the script:

 for MEMBERS

 with TOTALDUE > 90 ;

  list records

   LASTNAME in order ;

   highest of

This tells DataEase to read all the records (related to the current record being processed) in the related table and report the highest value in the column among those records. After the relational operator highest of, we must specify the name of the relationship followed by the name of the column whose value we want to include.

Specify the RESERVATIONS table name by double-clicking it in the Tables list. DataEase enters the table name into the script and updates the Columns list to display the names of columns in the RESERVATIONS table.

Specify the DATE column name by double-clicking it in the Columns list. DataEase enters it into the script. End the statement with a semicolon. The script now reads as follows:

 for MEMBERS

 with TOTALDUE > 90 ;

  list records

   LAST NAME in order ;

   highest of RESERVATIONS DATE ;

We want this procedure to list one more item: the membership fee paid by each member. This information is stored in the TOTAL DUE column in the MEMBERS table, so highlight MEMBERS in the Tables pick list. DataEase displays that table's column names in the Columns list.

Double-click TOTAL DUE in the Columns list to enter this item into the script, as shown:

 for MEMBERS

  with TOTALDUE > 90 ;

  list records

   LAST NAMEin order ;

   highest of RESERVATIONS DATE ;

   TOTAL DUE