error message-What to do?

[JIM]

Here's the SQL:
SELECT Invoices.InvoiceID, Invoices.InvoiceDate, Invoices.Denver,
Invoices.DatePerf, Invoices.WorkPerf, Invoices.WorkPerf, Invoices.Labor,
Invoices.Material, CCur(Nz(Invoices.Labor)+Nz([Material])+Nz([BalanceDue]))
AS BalDue, Invoices.WONo, tblWorkOrders.PONumber, tblCustomer.CustomerName,
tblCustomer.BillingAddress1, tblCustomer.BillingAddress2, tblCustomer.City,
tblCustomer.State, tblCustomer.Zip, tblCustomer.Location, tblSpecHand.[L&M],
tblSpecHand.TwoInv, tblSpecHand.PO, tblSpecHand.SHNotes,
tblClientBuildings.Region, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress4
FROM (((tblCustomer RIGHT JOIN Invoices ON tblCustomer.CustomerName =
Invoices.CustomerName) LEFT JOIN tblSpecHand ON tblCustomer.CustomerName =
tblSpecHand.CustomerName) INNER JOIN tblWorkOrders ON (Invoices.WONo =
tblWorkOrders.WONo) AND (tblSpecHand.CustomerName =
tblWorkOrders.CustomerName) AND (tblCustomer.CustomerName =
tblWorkOrders.CustomerName)) INNER JOIN tblClientBuildings ON
(Invoices.CustomerName = tblClientBuildings.CustomerName) AND
(Invoices.BuildNo = tblClientBuildings.BuildingNo) AND
(tblSpecHand.CustomerName = tblClientBuildings.CustomerName) AND
(tblCustomer.CustomerName = tblClientBuildings.CustomerName)
WHERE (((Invoices.InvoiceDate)=#1/19/2009#))
ORDER BY Invoices.InvoiceID;

I get message "The SQL statement could not be executed because it contains
ambiguous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement."
What does "include in your SQL statement" mean? Does it mean to create 2
queries then make 2nd query's source the first query? Do my relationships
need to be more descriptive? By descriptive, I mean perhaps Invoices needs
to be connected to tblWorkOrders(WONo) and to tblClientBuildings(BuildNo)
Here are relationships:
tblCustomer 1:1 tblSpecHand
tblCustomer 1:many with Invoices, tblWorkOrders, tblClientBuildings and
tblCustomerContacts.
TIA

 

[Michel Walsh]

Does it mean to create to queries then make 2nd query's source the first
query? YES

Do my relationships need to be more descriptive? NO (not necessary)


If you have A -> B <- C

Then, ( A -> B ) <- C (a)

can be different than
A -> ( B <- C) (b)


since in (a), we have all records in C while some in A may be missing and in
(b), have all records in A, while some in C can be missing.

So, do (A->C) or ( B<-C) in a query and then, use that query to complete
the join, ie, savedQuery <-C or A->someQuery


That happens because with Jet, the ( ) around join DO NOT describe the
relative priority of evaluation of the joins.



Vanderghast, Access MVP

Here's the SQL:
SELECT Invoices.InvoiceID, Invoices.InvoiceDate, Invoices.Denver,
Invoices.DatePerf, Invoices.WorkPerf, Invoices.WorkPerf, Invoices.Labor,
Invoices.Material,
CCur(Nz(Invoices.Labor)+Nz([Material])+Nz([BalanceDue]))
AS BalDue, Invoices.WONo, tblWorkOrders.PONumber,
tblCustomer.CustomerName,
tblCustomer.BillingAddress1, tblCustomer.BillingAddress2,
tblCustomer.City,
tblCustomer.State, tblCustomer.Zip, tblCustomer.Location,
tblSpecHand.[L&M],
tblSpecHand.TwoInv, tblSpecHand.PO, tblSpecHand.SHNotes,
tblClientBuildings.Region, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2, tblClientBuildings.JobAddress4
FROM (((tblCustomer RIGHT JOIN Invoices ON tblCustomer.CustomerName =
Invoices.CustomerName) LEFT JOIN tblSpecHand ON tblCustomer.CustomerName =
tblSpecHand.CustomerName) INNER JOIN tblWorkOrders ON (Invoices.WONo =
tblWorkOrders.WONo) AND (tblSpecHand.CustomerName =
tblWorkOrders.CustomerName) AND (tblCustomer.CustomerName =
tblWorkOrders.CustomerName)) INNER JOIN tblClientBuildings ON
(Invoices.CustomerName = tblClientBuildings.CustomerName) AND
(Invoices.BuildNo = tblClientBuildings.BuildingNo) AND
(tblSpecHand.CustomerName = tblClientBuildings.CustomerName) AND
(tblCustomer.CustomerName = tblClientBuildings.CustomerName)
WHERE (((Invoices.InvoiceDate)=#1/19/2009#))
ORDER BY Invoices.InvoiceID;

I get message "The SQL statement could not be executed because it contains
ambiguous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement."
What does "include in your SQL statement" mean? Does it mean to create 2
queries then make 2nd query's source the first query? Do my relationships
need to be more descriptive? By descriptive, I mean perhaps Invoices
needs
to be connected to tblWorkOrders(WONo) and to tblClientBuildings(BuildNo)
Here are relationships:
tblCustomer 1:1 tblSpecHand
tblCustomer 1:many with Invoices, tblWorkOrders, tblClientBuildings and
tblCustomerContacts.
TIA