ambiguous outter joins

[sokolln]

This has got me stumped. I am getting the ambiguous outter joins message in a query with only 2 tables. One table contains a list of names. The second table may contain those same names (once or multiple times) or it may not.

I want the join property to display all entries from this first table and only matching entries from the second table. When I select this option I get the ambiguous outter joins message.

Any help would be much appreciated

 

[namliam]

There are probably more than one joins specified between the two tables, all joins need to be set to the right join type seperatly...

 

[sokolln]

Nope, there is only one join between the table and that is the one I'm having issues with.

 

[namliam]

Yes one join, but multiple fields in the join...

If you have only 2 tables in your query this can be the only possible problem (that I can think of right now)

 

[sokolln]

I may not understand what you are saying.

I think there is just one field in the join. The query is joining the field "Ultimate Parent" from one table to the field "Ultimate Parent" in the second table.

 

[namliam]

The error "ambiguous outter joins" happens in two situations that I know of
1) With 3 or more tables and the links beeing wrong, left join + innerjoin or something.
2) 2 tables with a multifield join where one is set to an Inner join while the other set to an outer join.

I cannot think of any other reason at the moment. If you want you can zip and upload your DB so I can have a look see?

 

[MSAccessRookie]

It would really help in determining the problem if we could actually see the SQL code involved. Would that be possible?

 

[sokolln]

I've attached a snippet of the database with the problem. I am trying to run the sole query.

 

[namliam]

FROM [Ultimate Parent Table] 
INNER JOIN [Client Table] ON ([Ultimate Parent Table].[Ultimate Parent] = [Client Table].[Ultimate Parent]) 
                         AND ([Ultimate Parent Table].[Ultimate Parent] = [Client Table].[Ultimate Parent]);

Some how, I dont know how?? You managed to make the same join twice??? For some reason this SQL is generated by Access.

If you change the query to inner join and go to SQL you can see above.

Remove one of the joins and the problem is resolved...

I hate to say it, but .... I .... so

 

[MSAccessRookie]

I've attached a snippet of the database with the problem. I am trying to run the sole query.

You sent a Read Only copy of the database so I was unable to verify my findings, BUT:

I am not sure that this design is very normalized, but that aside (because I do not think it contributes to the problem), I noticed that there was no criteria set for the Joins. There needs to be a match criteria set on one of the Untimate Parent Fields that required the other one to be equal.

 

[sokolln]

Fixed! Thank you guys for your help.

 

[MSAccessRookie]

Fixed! Thank you guys for your help.

Always glad to be of assistance. Good luck in the rest of your project.

 

[MSAccessRookie]

FROM [Ultimate Parent Table] 
INNER JOIN [Client Table] ON ([Ultimate Parent Table].[Ultimate Parent] = [Client Table].[Ultimate Parent]) 
                         AND ([Ultimate Parent Table].[Ultimate Parent] = [Client Table].[Ultimate Parent]);

Some how, I dont know how?? You managed to make the same join twice??? For some reason this SQL is generated by Access.

If you change the query to inner join and go to SQL you can see above.

Remove one of the joins and the problem is resolved...

I hate to say it, but .... I .... so

I was never able to see this part. I based my assessment on a design view that had no criteria defined. How did you find this code? When I tried SQL View, I got the error that he referred to.

 

[namliam]

I was never able to see this part. I based my assessment on a design view that had no criteria defined. How did you find this code? When I tried SQL View, I got the error that he referred to.

Change the relation to an inner join, then you can switch to SQL view.

Your read only is probably because you have a different version from his, I had no problem with read-only at all, I am using A2002

 

[MSAccessRookie]

Change the relation to an inner join, then you can switch to SQL view.

Your read only is probably because you have a different version from his, I had no problem with read-only at all, I am using A2002

Thanks MailMan. What I like the most about this forum is that I can learn something new almost every day by reading about other people's issues and asking questions.

 

[namliam]

Happy to have helped both of you.