Query Table Joins

The Join section is only accessible when more than one table is selected in the Tables section. Joins are a way of joining together related records from separate tables.

Warning: Improper joins can result in invalid data and unpredictable query results. Allow FH to make entries where necessary, and only modify this area if you know exactly what you are doing.

1. From the Tools menu, select the Queries option.

   The Queries form is displayed.
   

2. Select the module and record area, and either create a new query, or modify an existing query.

   Information on creating or modifying a query is available in Create New Query and Modify Query.

3. Verify that Report is selected for Query Type(s) for the query.

4. Click the Join tab.

   • Properties

   • Tables

   • Fields

   • Join

   • Filter

   • Order By

   • Group By

   • Having

   • Pivot Table

5. Add a table join.

   Joins are automatically added when multiple tables are added and the tables share a common ID field. When more than one table is added, you are prompted to specify the join type. You will generally accept the default. Join types are discussed in greater detail below. You need not add additional table joins unless you have very specific reasons for doing so.

   1. Press Add.

      Inner is inserted into the Type column.

   2. Click the Type column to display other join types.

      Join type	Does this
      Inner	Only the records that match the join condition from both the source and target tables are included.
      Left	All rows from source table and only matching records from the target table.
      Right	All rows from target table and only matching records from the source table.
      Outer	All matching & non-matching records from both tables.

   3. Click in the Source Table column to display available source tables and select the source table.

      The Source Table is where specified information originates from.

   4. Click in the Target Table column to display available target tables and select the target table.

      The Target Table is where the information is imported to.

6. Add a join expression.

   A join expression is automatically added when multiple tables with ID fields in common are added. Each table join has its own join expressions. When you add an additional table join, you need to specify additional criteria for applying the join condition. To add an additional join:

   1. Press Add in the Join Expressions area.

      The cursor moves to the Type column with "Join” selected.

   2. Click in the Type column to change the join expression type.

      Join type	Does this
      Join	Compare two field values.
      Value	Compare field with values you specify.
      Var	Compare a field to a variable value.

      Warning: There is an untitled column for nesting join expressions. This advanced feature should not be used unless you are familiar with nesting join expressions.

   3. Click in the Source column and select the field from the source table.
   4. Check Not if you wish to exclude records where the join expression is true.

   5. Click Criteria column and specify the criteria operator.

      Information on the criteria operator is available in Criteria Operators.

   6. Click in the Target column and select the field from the source table.

   7. Check Case if you wish to ignore capitalization as criteria for evaluating data.

      Example: FH sees “FoxTrot” and “foxtrot” as separate values, even though they use the same letters. Typically not necessary since ID fields are all uppercase.

      Warning: There is an untitled column for nesting join expressions. This advanced feature should not be used unless you are familiar with nesting join expressions.

   8. Click in the Logical column if additional join expressions are to be added and they are required.

      Join expression	Does this
      Or	Adds an Or between multiple criteria, so records will still be found even if the first criteria is not met. Example: You specify records with specific values in two fields or multiple values in one field be included in a report. You want records included even if the first criteria is not met, add two “Literal Comparison” criteria with an Or between them. If you don’t add an Or between two criteria and the first criteria is not met, no records will be included in the results table even if the second criteria is met.
      Add	Adds an And between multiple criteria, so records will only be found if the first criteria and the second criteria are met.

 

Related Topics:

• Query Order Results