Windows 7 32bit Dealing with the “ambiguous outer

Today’s guest blogger is Colin Wilcox,Microsoft Office Professional Plus 2007, writer for Access Training on Office Online. When you query multiple tables for data,Microsoft Office 2007, 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,Microsoft Office Professional Plus 2007, 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,Microsoft Office 2010 Key, and an equi-join between Table1 and Table2. A left outer join between Table1 and Table2,Windows 7 32bit, 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. Have an Access Power Tip that you want to share? Send it to Mike and Chris at accpower@microsoft.com. <div