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