When you include multiple data sources in a query, you use JOINS to limit the records that you want to see based on how the data sources are related to each other. You must use JOINS to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results. JOINS in MS Access is just the same as any other Relational Database Management System (RDBMS) like MySQL

You can also join queries in the same way that you join tables and can also join both tables and queries. Joins behave similarly to query criteria in that they establish rules that the data must match before they are included in the query operations. Unlike criteria, joins also specify that each pair of rows that satisfy the join condition will be combined in the record set to form a single row.

I explained table joins or relationship in chapter 2 (part 2) at http://www.microsofttut.com/2016/07/table-operations-and-relationshipsjoins.html. You may need to revise it because it is a prerequisite to Join Queries.

This is part 3 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to create a JOIN Query in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions. I will also explain object joins or relationship and then give you some practical exercises.

In the previous part (Part 3) at http://www.microsofttut.com/2017/04/practical-application-of-sql-in-ms-access-mysql.html, I explained UNION query SQL in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

TYPES OF QUERY JOINS

There are many types of query joins, but I will discuss only the five most common JOIN types. They include:
1. Inner Join
2. Left Outer Join
3. Right Inner Join
4. Full Outer Join
5. Cross Join

NOTE:

To create query joins in MS Access:
Create an empty SELECT query through the Query Design option. Then switch the empty SELECT query over to SQL view where you can type your SQL statements directly.

INNER JOIN:
Inner Join shows only rows where a common value exists in both of the joined tables. They are the most common type join. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be returned. For example, the tables below are used for storing information of emergency patients and those of regular patience in a Health Clinic. The first table is named emergency_patients and the second, regular-patients.
Emergency patients table

Regular patients table

Referring to the two tables above, if you want to show the ID and HOME TOWN fields and return only the records of emergency patients that are also regular patients, then your inner join query SQL would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E INNER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns four records that appear in both tables as shown below.
Also note how I aliased the table names. Note also, the ON clause used here in place of the WHERE clause, you have Join Queries.

Inner join query result

LEFT OUTER JOIN:
When a query with a left outer join is run, it returns all the records from the first (left) table and the corresponding records from the second table that have a matching value in the join field of the first table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records of emergency patients, including the records of regular patients that are emergency patients, then your left join SQL statement would resemble this:

SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID];

The query returns 6 records as shown below.

Left outer join query result

Two records returned in the screenshot above have empty data under the HOME TOWN field because the emergency_patients table don’t have the HOME TOWN field. So the query returned empty spaces.

RIGHT OUTER JOIN:
When a query with a right outer join is run, it returns all the records from the second (right) table and the corresponding records from the first table that have a matching value in the join field of the second table.

For example, still referring to the above two tables, if you want your query to show the ID and HOME TOWN fields and return all the records or regular patients, then your SQL statement should resemble this:
Click http://www.microsofttut.com/2017/04/how-to-create-join-queries-in-ms-access.html to read full post with screen to screen images.