Access: Joining 3 tables (using left joins) in Access 2003/XP/2000/97

http://www.techonthenet.com/access/queries/joins1.php

Access: Joining 3 tables (using left joins) in Access 2003/XP/2000/97

---

Question:  In Access 2003/XP/2000/97, how do you link 3 tables together, where table 1 is linked on one field to table 2, and the table 3 is linked to table 2 on another field?

In the result, I want the query to display columns from table 1, 2, 3 having table 1 as the primary one. I want all the corresponding records from all tables and zeros for empty records. Also the desired columns from the 3 tables should show.

Answer:  To retrieve your data, you will need to perform multiple left or right joins. In this example, we'll demonstrate how to accomplish this query using left joins.

For the purposes of this example, we'll assume that:

Table 1 = Employees
Table 2 = Orders
Table 3 = Order Details

First, create a new query and add all 3 tables to the query.

Right click on the "join line" between the Employees table and the Orders Table and select "Properties" from the popup menu.

When the Join Properties window appears, select the second option and click on the OK button.

Your query should now look like this. You will notice an arrow on the right-side of the join line. (between the Employees table and the Orders table)

Next, right-click on the join line between the Orders table and the Order Details table and select Properties from the popup menu.

When the Properties window appears, select the second option and click on the OK button.

Next, select the fields that you want in the query results. We've chosen the fields below.

Now when you run the query, you will see blank values in the Order Date and Quantity fields. This is because there were no matching records in the corresponding tables based on the join criteria.