What is meant by the 'ambiguous outer join' error and how do I get around it?

[wmphoto]

To quickly sum up what I am trying to do, I have Query 1 and Query 2.

Query 1 has fields A and B, which correspond with Query 2 fields X and Y.
Query 1 also has field C which contains the values I want to access.

What I want is to create a field Z in Query 2, which displays the value C where A=X and B=Y

Whenever I try to create a join between Queries 1 and 2, or create an expression for field Z which contains an SQL statement to select C from Query 1 where A=X and B=Y, I get the 'ambiguous outer join' error.

 

[MSAccessRookie]

To quickly sum up what I am trying to do, I have Query 1 and Query 2.

Query 1 has fields A and B, which correspond with Query 2 fields X and Y.
Query 1 also has field C which contains the values I want to access.

What I want is to create a field Z in Query 2, which displays the value C where A=X and B=Y

Whenever I try to create a join between Queries 1 and 2, or create an expression for field Z which contains an SQL statement to select C from Query 1 where A=X and B=Y, I get the 'ambiguous outer join' error.

Seeing the SQL Code would be helpful here, as this particular error has a number of possible causes. Since it will often occur due to having multiple JOINs without specifying a particular order, you can start looking there, and post the SQL Code if you do not find anything. Remember to post all of the SQL Code in the even theat there are separate Sub-Queries.

 

[wmphoto]

Seeing the SQL Code would be helpful here, as this particular error has a number of possible causes. Since it will often occur due to having multiple JOINs without specifying a particular order, you can start looking there, and post the SQL Code if you do not find anything. Remember to post all of the SQL Code in the even theat there are separate Sub-Queries.

Thanks, the SQL code was something like:
SELECT C FROM 1 WHERE A=X AND B=Y

I tried going into the queries and creating a single field which combines the data for both A&B/X&Y... I was able to do this for X&Y but not for A&B... I got a 'type mismatch error'.

I don't know if it has something to do with the fact that Query 2 is selected from Query 1... but basically Query 2 is a query with totals, where A&B are the fields X&Y set to 'group by' and C is the sum of a different field. In short I want a field in Query 1 to show the sum of all the other records with the same values for A&B.

 

[MSAccessRookie]

Thanks, the SQL code was something like:
SELECT C FROM 1 WHERE A=X AND B=Y

I tried going into the queries and creating a single field which combines the data for both A&B/X&Y... I was able to do this for X&Y but not for A&B... I got a 'type mismatch error'.

I don't know if it has something to do with the fact that Query 2 is selected from Query 1... but basically Query 2 is a query with totals, where A&B are the fields X&Y set to 'group by' and C is the sum of a different field. In short I want a field in Query 1 to show the sum of all the other records with the same values for A&B.

Unfortunately, "something like" might not be close enough to make an assessment. From what you have said so far, all I can assume is the following three things:

1. A&X are the same type (does not matter what type for the moment).
2. B&Y are the same type (does not matter what type for the moment).
3. The two types above are NOT the same type.

These assumptions will create the scenario that you describe. Because the types of A&B, and X&Y are different, they might not be compatible, and could give a type mismatch when they are compared to each other. If this is the case, then in order to compare them to each other, a conversion would have to take place first.

If you would like to try again, please let us know the exact SQL code that fails, and also the data type of each of the fields involved. Odds are that by the time you have done that, you may be able to figure it out on your own, but if not, we will be able to provide much more detailed assistance. Good luck until then

-- Rookie