Ambiguous outer join problems

I

irato

Im trying to help someone out with a problem they have been having with
an access application they use alot. The problem is he uses a form in
the application and recently he has not ben able to get into the form
because the query which is the datasource to the form wont execute
anymore. Hes not sure why its happened all of a sudden but when you try
to execute the query now the "cant execute query, ambiguous outer
joins..." error comes up. I know a bit about sql but i am hesitant to
rewrite it because im not sure how the outer join translates to the way
i was taught to do joins
(select c.* from c,d where c.e = d.e) and i dont want to rewrite it in
a way that changes the results.

Can anyone see any reason why this messy block of code wont work or
think of a different way to write it.

SELECT MatA.*,
QProd_BaseKPINS.ContactCode,
QProd_BaseKPINS.PropertyName,
QProd_BaseKPINS.PropertyAddress1,
QProd_BaseKPINS.PropertyAddress2,
QProd_BaseKPINS.PropertyAddress3,
QProd_BaseKPINS.Area,
QProd_BaseKPINS.Alt,
QProd_BaseKPINS.AltG,
QProd_BaseKPINS.SalutationOrNull,
QProd_BaseKPINS.BusinessPhone,
QProd_BaseKPINS.MobilePhone,
QProd_BaseKPINS.HomePhone,
QProd_BaseBlocks.Variety,
QProd_BaseBlocks.GrowMeth,
QProd_BaseBlocks.Rep,
QProd_BaseBlocks.OrchMgr,
IIf([Pick]="no","No","Yes") AS Picked
FROM ((
MatA LEFT JOIN QProd_BaseKPINS ON MatA.PropertyCode =
QProd_BaseKPINS.PropertyCode)
LEFT JOIN QProd_BaseBlocks ON MatA.BlockCode =
QProd_BaseBlocks.BlockCode)
LEFT JOIN QMatANotPicked ON MatA.MatAId = QMatANotPicked.MatAId
ORDER BY MatA.MatAId;
 
J

John Vinson

Im trying to help someone out with a problem they have been having with
an access application they use alot. The problem is he uses a form in
the application and recently he has not ben able to get into the form
because the query which is the datasource to the form wont execute
anymore. Hes not sure why its happened all of a sudden but when you try
to execute the query now the "cant execute query, ambiguous outer
joins..." error comes up. I know a bit about sql but i am hesitant to
rewrite it because im not sure how the outer join translates to the way
i was taught to do joins
(select c.* from c,d where c.e = d.e) and i dont want to rewrite it in
a way that changes the results.
Click to expand...

This older syntax does not directly support outer joins of either
direction: in ORACLE the syntax had a (+) on one of the tablenames to
indicate that this was an outer join. Access does not support this
syntax, using instead the ANSI92 JOIN syntax.
Can anyone see any reason why this messy block of code wont work or
think of a different way to write it.

SELECT MatA.*,
QProd_BaseKPINS.ContactCode,
QProd_BaseKPINS.PropertyName,
QProd_BaseKPINS.PropertyAddress1,
QProd_BaseKPINS.PropertyAddress2,
QProd_BaseKPINS.PropertyAddress3,
QProd_BaseKPINS.Area,
QProd_BaseKPINS.Alt,
QProd_BaseKPINS.AltG,
QProd_BaseKPINS.SalutationOrNull,
QProd_BaseKPINS.BusinessPhone,
QProd_BaseKPINS.MobilePhone,
QProd_BaseKPINS.HomePhone,
QProd_BaseBlocks.Variety,
QProd_BaseBlocks.GrowMeth,
QProd_BaseBlocks.Rep,
QProd_BaseBlocks.OrchMgr,
IIf([Pick]="no","No","Yes") AS Picked
FROM ((
MatA LEFT JOIN QProd_BaseKPINS ON MatA.PropertyCode =
QProd_BaseKPINS.PropertyCode)
LEFT JOIN QProd_BaseBlocks ON MatA.BlockCode =
QProd_BaseBlocks.BlockCode)
LEFT JOIN QMatANotPicked ON MatA.MatAId = QMatANotPicked.MatAId
ORDER BY MatA.MatAId;
Click to expand...

The outer joins ARE ambiguous because there might not *be* any record
in QProd_BaseKPINS in the innermost join, but you're joining from
PropertyCode in that table to BlockCode - and similarly in the join
from MatA to QMatANotPicked.

You may need to create and store one or more outer join queries and
link them in an another query.
 
Top