Using The Tables and Relations Section

The Tables and Relations section on the Query tab displays the tables that are being used by the query, and the join relations that are required to exist between them.  Only records from those tables that satisfy the required relationships will be returned when the query is executed.

Here are the tasks that can be performed within Tables and Relations:

Adding Tables to the Query

To add tables to your query, you can do one of the following:

  1. Expand the tree of tables on the left-hand side of the Query tab until you see the table you want to add to the query. Double-click the table, or select it and and invoke the pop-up menu and select Add to Query. The table should now appear in the Tables and Relations section.
  2. Go to the Insert pull-down menu, and select Insert Table, or press the Insert Table toolbar button. A dialog with a tree of tables will appear. Expand the tree to the table you want to add and press the Add button. Repeat until all the tables you want are added to the query. Press the Close button to close the dialog.

Adding Columns to the Query

To add columns to the query, double-click the column name from the table in the Tables and Relations section, or select the column, and invoke the pop-up menu, and then select Add To Results. The column should turn bold on the table, and also appear on the bottom section of the Query Tab.

Editing Relationships between Tables (Joins)

What is a relationship or join?

With more than one table in a query, you will most likely want to join them based on values in certain columns. The most common relationship is an inner join, which matches values in a column in one table to another column in another table. For example, with an Invoice table and a Customers table, a join on Invoice.Billedto and Customer.Customer would match all invoice records to a customer, and you could select Customer information that matches the invoices to that customer.

Create a relationship

To create a relationship between columns, select one column, and then drag to the column in the other table that you want to join. A line will connect the two columns. You can have multiple joins between tables. Multiple joins will narrow the matching criteria - all the join pairs must match for the record to be in the results.

Change a relationship

The most typical join relationship is an equi-join, where the values in the columns match exactly. You can change this relationship to anything else (such as table1.col_a > table2.col_a) by selecting a join line,  invoking the pop-up menu, and selecting Edit Relationship. You then use the Expression Editor to create any expression you need.

Delete a relationship

To delete a relationship, select the line that represents the relationship, and hit the delete key. Or invoke the pop-up menu, and select Delete Relationship

Removing A Table

To remove a table from your query, select the table, and hit the delete key on your keyboard. Or, select the table, invoke the pop-up menu, and select the Delete Table item.

A confirmation box will display, and you must press the YES or NO to continue or stop the table deletion. This operation will only delete the table from the Query. It will not physically delete the table.