Page 10 of 13PREVNEXT

Queries VII: Query multiple sources for data

Relationships that allow for an indirect join

A way to join unrelated data.

Finally, in some cases, you may not be able to join some sources unless they share a relationship through an intermediate source. The picture shows this. You can join the Employees and OrderDetails tables only because they're joined to the Orders table.

If you include the intermediate table in your record source, Access will try to join the surrounding tables automatically. In this case, it can join the OrderDetails and Employees tables because a primary key is involved — the OrderID field in the Orders table.

Remember, when you use an indirect join, always include the intermediate table in your record source. You don't have to include it in your results, but your query will fail without that table. For example, if you remove the Orders table from the query, you'll get a Cartesian product — a lot of useless data. If you try to join the Employees and OrderDetails tables directly, you'll get no results because none of the records match.

You'll create an indirect join in the practice, so keep going.

Page 10 of 13PREVNEXT