Access 2010

Joining tables in queries

The final thing you need to consider when designing a query is the way you link, or join, the tables you're working with. When you add two tables to an Access query, this is what you'll see in the Object Relationship Pane:

Joined tables in the Object Relationship Pane

The line connecting the two tables is called the join line. See how the join line is actually an arrow? This is because it indicates the order in which the query looks at data from the two tables. In the image above, the arrow is pointing from left to right, which means the query will look at data in the left table first, then only the data in the right table that relates to the records it's already seen in the left table.

Your tables won't always be joined this way—sometimes Access will join them right to left. In either case, you might need to change the direction of the join to make sure your query includes the correct information. The join direction can affect which information your query retrieves.

To understand what this means, consider the query we're designing. For our query, we need to see customers who have placed orders, so we've included the Customers table and the Orders table. Let's take a look at some of the data contained in those tables.

Related data stored in the Customers and Orders tables

What do you notice when you look at these lists? First of all, every single order in the Orders table is linked to someone in the Customers table—the customer who placed that order. However, when you look at the Customers table, you'll see that the customers who've placed multiple orders are linked to more than one order, and those who've never placed an order are linked to no orders at all. As you can see, even when two tables are linked it's possible to have records in one table that have no relationship to any record in the other table.

So what happens when Access tries to run our query with the current join, left to right? First, it pulls every record from the table to the left, our Customers table.

The Left to Right join retrieves all the records from the table on the left first.

It then retrieves every record from the right table that has a relationship with a record Access has already taken from the left table.

The query then retrieves the orders linked to the customer records it already pulled. These are the records the query will draw its information from.

Since our join began with the Customers table, our query will include records for all of our customers, including those who never placed orders. This is way more information than we want! We only want to see records for customers who have placed orders.

Fortunately, we can fix this problem by changing the direction of the join line. If we join the tables from right to left instead, Access will first retrieve all of the orders from the right table, our Orders table:

The Right to Left join retrieves all the records from the table on the right first.

Then Access will look at the left table and retrieve only the records of customers who are linked to an order on the right.

Next, Access retrieves only the records from the left table that are linked to existing orders from the right. These are the records the query will draw its information from.

We now have exactly the information we want: all of the customers who have placed an order, and only those customers. As you can see, we had to join our tables in the correct direction to obtain the information we wanted.

Now that we understand which join direction we need to use, we're ready to build our query!

In our query, we needed to use the right to left join, but the correct join direction for the tables in your queries will depend on what information you want to see and where that information is stored. When you add tables to a query, Access will automatically join the tables for you, but it often doesn't join them in the correct direction. This is why it's important to always review the joins between your tables before you build a query.