Any Joins and Related Record Joins


In this topic you will learn how to create a query that combines fields from three tables/records (Personal_Data, Job and Jobcode) using the two predefined joins Record Hierarchy join and a Related Record join.

 

In Query, predefined joins can be generated as a Related Record join. Since these types of joins are predefined, you will not have to add any criteria to manually link the records.

 

Any Joins gives you the ability to join any record, that you may access, to your currently selected record. Records will be joined on their common high-level keys. If you have an Any Join, it will automatically be added to your criteria, unless there is only one row of data in the record to be joined.


Steps:

  1. Click the Main Menu button.

    Step 1
  2. Point to the Reporting Tools menu.

    Step 2
  3. Point to the Query menu.

    Step 3
  4. Click the Query Manager menu.

    Step 4
  5. Click the Create New Query link.

    Step 5
  6. Enter the desired information into the begins with field. Enter "personal".

    Step 6
  7. Click the Search button.

    Step 7
  8. Click the Add Record link for the Personal_DATA record.

    Step 8
  9. Note: Query displays a letter in front of each record or table. This letter is an alias that represents the record or table. When joining records or tables, Query assigns subsequent letters.

     

    Click the checkbox EMPLID.

    Step 9
  10. Click the checkbox for NAME.

    Step 10
  11. Note: This is a Any join.

     

    Click the Records tab.

    Step 11
  12. Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "job"
    .

    Step 12
  13. Click the Search button.

    Step 13
  14. Click Join Record for the JOB - EE Job History.

    Step 14
  15. Click the A = PERSONAL_DATA - PERSONAL_DATA for Rpting link.

    Step 15
  16. Note: The Query tool has detected alike fields in both queries. Always select Add Criteria on this screen.

     

    Click the Add Criteria button.

    Step 16
  17. Click the OK button.

    Step 17
  18. Click the checkbox for DEPTID.

    Step 18
  19. Click the checkbox for JOBCODE.

    Step 19
  20. Click the Join JOBCODE_TBL - Job Codes link.

     

    Note: This is a Related Record join.

    Step 20
  21. Click the OK button.

    Step 21
  22. Click the OK button.

    Step 22
  23. Click the checkbox for DESCR.

    Step 23
  24. Click the Fields tab.

    Step 24
  25. Note: Query displays a letter in front of each field. This letter is an alias that represents the record or table from which the field was pulled. When joining records or tables, Query assigns subsequent letters.

     

    Click the Save As link.

    Step 25
  26. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "EMPLOYEE_JOB_TITLES"
    .

    Step 26
  27. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "Employee and Job Titles"
    .

    Step 27
  28. Click the OK button.

    Step 28
  29. Click the Run tab.

    Step 29
  30. Step 30

Table of Contents  Start Topic