Page 3 of 8PREVNEXT

Queries VIII: Dealing with ambiguous joins

Types of ambiguous joins

Three ambiguous join structures.

If you took the seventh course in this series, you learned about outer joins, and about base and secondary tables. Outer joins become ambiguous whenever a secondary table participates in another join of any kind. Your query fails because Access has no way of knowing which data to join first. The process works like this:

An outer join returns all the data from a base table and only matching data from a secondary table. When the secondary table participates in another join of any kind, that second join also tries to match records. When that happens, Access has no way of matching the data that both joins try to return. Put another way, which tables get joined first, A and B or B and C?

So how do you spot an ambiguous join? The picture shows some typical ambiguous structures. In each case, the secondary table participates in another join, and the type of join doesn't matter. Any of the structures shown in the picture will trigger the ambiguous outer joins message.

To work around the problem, you create two queries. The first query retrieves data only from the tables involved in one of the joins. The second query uses the first query as part of its record source, along with the third table involved in the other join. Keep going to see how this works.

Page 3 of 8PREVNEXT