You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest blogger is Colin Wilcox, writer for Access Training on Office Online.
When you query multiple tables for data, you sometimes see a message about “ambiguous outer joins.” The message tells you to create a separate query that performs one of the joins, and then include that first query in your SQL statement.
You're seeing that message because of the join structure in your query. Whenever an outer join points to a table, and that table also participates in another join of any kind, your query can't run. Access doesn't know which join to make first, because it can't match the records returned by each join.
When you see that message, look at the joins involved in your query. Any time you have an outer join on one side, and a join of any kind on the other, you have an ambiguous structure. Here are some visual examples. All of these structures will cause Access to display the “ambiguous joins” message:
A left outer join between Table1 and Table2, and an equi-join between Table2 and Table3.
A right outer join between Table3 and Table2, and an equi-join between Table1 and Table2.
A left outer join between Table1 and Table2, and a right outer join between Table2 and Table3.
To solve the problem, create two queries. In the first, retrieve the data from the tables that participate in one of the joins. In the second query, use the first query as part of your record source, and retrieve data from the tables involved in the other join.
Comments: (5) Collapse
Nice work Colin. This will help many beginners with this common problem. What really makes it a great post is the illustrations. I will be adding more illustrations to my blog as well. Love the clean look of the Microsoft Access Team Blog as well. Maybe one day I will be chosen as a guest blogger.
This works ... if you are able to add queries. Such is not always the case. I have tried techniques such as subqueries, like the following:
SELECT ...
FROM (SELECT ThisTable.Key, MoreData FROM ThisTable LEFT JOIN ThatTable ON ThisTable.Key = ThatTable.Key ) ResultTable
LEFT JOIN AnotherTable
ON AnotherTable.Key = ResultTable.Key You can trick Access into doing this (which was a slight surprise to me, Kudos!!!), but DON'T let the query get munged by the QBE editor!
I don't agree with the statement:
"Whenever an outer join points to a table, and that table also participates in another join of any kind, your query can't run." In fact, if an outer join points to a table, and that table points to another table with an outer join, then your query will run. The problem is when your arrowheads point toward each other, or toward an inner join. The two simple rules I use to avoid ambiguous outer joins are: 1. All outer joins (arrowheads) must point away from the "main table(s)".
2. The "main table(s)" may be a single table, or may be a group of tables joined with inner joins (no arrowheads).
If you make 2 queries, sometimes the first has to be huge. If you can resolve it by directly editing the sql code like Kevin showed the problem is easily solved.
Kevin: Thanks for posting that trick! Armen: I'll pass on your comment and we'll make sure our Help and Training content is accurate on that point. Thanks!
Chris
Comments: (loading) Collapse