Ambiguous OUTER JOIN in query

[sbraun27]

Hello everyone!

I have a query in access (I did not build and has worked for 13 years) that all of a sudden is not working anymore and gives the "Your SQL query could not be executed because it contains ambiguous outer joins". I'm not very adept at SQL so I am struggling on fixing the issue. The query is:

SELECT TaxProxy_Account.[Checked On], TaxProxy_Account.Account, TaxProxy_Account.Sell_Fund_1, TaxProxy_Account.Sell_Fund_2, TaxProxy_Account.ProxyHurdle AS Hurdle, TaxProxy_Account.PercentSell_1, TaxProxy_Account.PercentSell_2, TaxProxy_Account.ProxyHurdle, TaxFactor_XTaxFactor_Sum.SumOfWeight, IIf((([PercentSell_1]/[SumOfWeight])*[SumOfXTaxFactor])>50,50,(([PercentSell_1]/[SumOfWeight])*[SumOfXTaxFactor])) AS Account_Factor, TaxFactor_XTaxFactor_Sum.SumOfXTaxFactor, [Hurdle]-[Account_Factor] AS Diff, TaxProxy_Account.[Review Date], TaxProxy_Account.Buy_Fund_1, TaxProxy_Account.Model

FROM TaxFactor_XTaxFactor_Sum AS TaxFactor_XTaxFactor_Sum_1, TaxProxy_Account LEFT JOIN TaxFactor_XTaxFactor_Sum ON (TaxProxy_Account.Account = TaxFactor_XTaxFactor_Sum.[Portfolio Account Number]) AND (TaxProxy_Account.[Sell_Fund_1] = TaxFactor_XTaxFactor_Sum.Symbol);

Any help on rewriting this query would be greatly helpful.

 

[ranman256]

instead of SQL try putting it into a query. It may help.
Or try rebuilding the sql in a query from scratch. Your OUTER joins must happen to ALL joins. This error occurs when only 1 side of joins are outer, and others are inner.

 

[sbraun27]

As far as I can tell, the only join is the left join in the FROM section. Is it a matter of a missing parentheses? I can't see anything that would lead to the join being ambiguous, and there aren't any outer joins in the statement.

 

[xenou]

Your sql looks pretty garbled right now. Based on what you posted there is an entire table in the from clause that is not used in the query at all:

FROM 
[B][COLOR="#FF0000"]	TaxFactor_XTaxFactor_Sum AS TaxFactor_XTaxFactor_Sum_1, [/COLOR][/B]
	TaxProxy_Account 
		LEFT JOIN TaxFactor_XTaxFactor_Sum 
		ON (TaxProxy_Account.Account = TaxFactor_XTaxFactor_Sum.[Portfolio Account Number]) 
		AND (TaxProxy_Account.[Sell_Fund_1] = TaxFactor_XTaxFactor_Sum.Symbol);

So you can and should delete that whole line and make the from clause like this:

FROM 
	TaxProxy_Account 
	LEFT JOIN TaxFactor_XTaxFactor_Sum 
	ON (TaxProxy_Account.Account = TaxFactor_XTaxFactor_Sum.[Portfolio Account Number]) 
	AND (TaxProxy_Account.[Sell_Fund_1] = TaxFactor_XTaxFactor_Sum.Symbol);

... unless there is some weird reason for that to be there that I don't understand right now.