- By Andrew Couch
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
Creating an additional query to resolve a problem with mixed joins
We have a query that links together Orders, Order Details, and Products. Because we want to also show any Orders without Order Details, the join from Orders to Order Details is set to an outer join to include all Orders with or without Order Details. The join to products has been left as a matching join. This mixture causes a join ambiguity problem.
In our example, we could change the join between Order Details and Products to an outer join, but we want to demonstrate the solution that is proposed by the text in the join ambiguity warning box, where the query is changed to use another query that contains the outer join between Orders and Order Details.
Resolve join ambiguity
Click each of the tables where the join is different, and press Delete to remove the tables from the query.
Having created another query that joins together the tables we removed (with the outer join), add this query to the query design.