error message-What to do?

J

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
 
M

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


JIM said:
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
Click to expand...
 
Top