Query for a joining table creating ambiguous join

[BlueJacket]

Hello! I tried searching for this topic, but didn't find anything, so sorry if this is a repeat.

I attached a zip file with an image of the three tables I'm using to create a query. I have a Judges table and a County Information table, and since there are some cases where judges rotate through several counties and each county has multiple judges, my research showed me a joining table would be the best way to create this many-to-many relationship.

When I go to run a query based off of these three tables, I get an error message saying the SQL statement could not be executed because it contains ambiguous outer joins. Every other video/webpage I've found that shows how to make forms based off of many-to-many relationships, none of them run into this error.

How do I fix this? I know subqueries are what needs to happen in this instance, but I feel like I'm doing something wrong to even get that error.

Thanks in advance.

 

[Ranman256]

the ambiguous joins come from ONE of them having a outer join.
if one has an outer join you should make them all that way in the same direction.
It stops the error.

 

[The_Doc_Man]

They key to junction tables is that they have two relationships (minimum), a many-to-one with each of the two other tables being joined.

To make a query that joins all three at the same time, base the query on the joining table itself. I'll try to draw this

Table1 <1 to many< Junction Table >many to 1> Table 2

Junction  >many to 1> table 1
Table      >many to 1> table 2

The first example will fail flat out because the arrows have to point in two different directions from the junction table. You can lay this out using the query design grid by pulling in the junction table and both tables, but be sure that you take the fields with the foreign keys (which are in the junction table) as the fields that you want to see in the final query. Other field values can come from tables 1 and 2.

If that doesn't work, do it as 2 querys - one on table 1 and the junction, then the 2nd query on table 2 and the first query. That is the other way to make this work.

Ranman also provided a similar concept. Just remember, it really ISN'T about the arrows. It is about what the arrows represent - a many-to-one relationship - and the representational arrow always points towards the "one" side.

 

[BlueJacket]

Question: Would it also work if I set the join to only include rows where the join fields from both tables are equal for both relationships?

 

[The_Doc_Man]

To what point would you make that restriction? What would be the goal?

If you have a "unique combinations" requirement, you do that by making the Prime Key of the junction table be a compound key comprised of the two individual keys from the two tables being combined this way.