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.

Enter the desired information into the begins with field. Enter "personal".

Click the Add Record link for the Personal_DATA record.

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.

Click the checkbox for NAME.

Note: This is a Any join.

Click Join Record for the JOB - EE Job History.

Click the A = PERSONAL_DATA - PERSONAL_DATA for Rpting link.

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

Click the checkbox for DEPTID.

Click the checkbox for JOBCODE.

Note: This is a Related Record join.

Click the checkbox for DESCR.

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.

Any Joins and Related Record Joins

Enter the desired information into the begins with field. Enter a valid value e.g. "personal".

Click the Main Menu button.

Click the Create New Query link.

Enter the desired information into the begins with field. Enter

a valid value

e.g.

"personal"

Click the Search button.

Click the Add Record link.

Click the Select option.

Click the Records tab.

Enter the desired information into the begins with field. Enter

a valid value

e.g.

"job"

Click the Search button.

Click the Join Record link.

Click the A = PERSONAL_DATA - PERSONAL_DATA for Rpting link.

Click the Add Criteria button.

Click the OK button.

Click the Select option.

Click the Join JOBCODE_TBL - Job Codes link.

Click the OK button.

Click the Select option.

Click the Fields tab.

Click the Save As link.

Enter the desired information into the Query field. Enter

a valid value

e.g.

"EMPLOYEE_JOB_TITLES"

Enter the desired information into the Description field. Enter

a valid value

e.g.

"Employee and Job Titles"

Click the OK button.

Point to the Reporting Tools menu.

Point to the Query menu.

Click the Query Manager menu.