When creating a query, it may be necessary to retrieve data from more than one table (record) or specify criteria in your query from a second table. In these cases, you need to link at least two tables in one query. Query enables you to run a query that pulls information from multiple tables. When you perform a join, the records involved are linked based on common fields.
Linking multiple tables, or joining, enables Query to retrieve data from more than one table, but the query output will appear as if the data is retrieved from a single table.
Working with multiple tables is almost as easy as working with one.
The three joins that are covered in Query Intermediate are the following:
1. Record Hierarchy join
2. Related Record join
3. Any join
In this lesson you wish to retrieve a query that provides a list of employees with their EmplID, Job Code, DeptID, Name and Job Title (Descr). The Personal_Data record contains the EmplID and Name fields. The Job record contains the Job Code and DeptID fields, but not the Descr field. The Descr field is found in the Jobcode table. Therefore, it will be necessary to pull the fields from three different tables (Personal_Data, Job and Jobcode) to retrieve the desired query results.