Cartesian product & outer join

[Ann Scharpf]

Did not get any response to my third level reply, so I thought I'd try
posting anew. Thanks!
_________________________________
Here is the SQL view as you and John Vinson directed:

SELECT [Civilian Payroll].[Employee Name], Round(([CY1 End Date]-[FY Start
Date])/365,2)*[Salary] AS SalaryCY1, Round(([FY End Date]-[CY2 Start
Date])/365,2)*[Salary]*(1+[COLA]) AS SalaryCY2,
(([SalaryCY1]+[SalaryCY2]))+(([SalaryCY1]+[SalaryCY2])*[BenefitsPercent])+[Bonus] AS TotalSalaryBenefitsBonus, CivilianOvertimeCost.OTcost AS Expr1
FROM [Standard Variables], [Civilian Payroll] LEFT JOIN
CivilianOTCostOutputTable ON [Civilian Payroll].[Employee Name] =
CivilianOTCostOutputTable.EmployeeName;

Thanks for helping me out!

 

[[MVP] S.Clark]

An Ambiguous Outer Join basically means that your arrows are pointing the
wrong way, typically when there are more than two tables and more than one
Outer Join.

What is the right way? Well, I can't say I know for your exact case, but
I'll give an example from everyone's favorite Northwind database.

SELECT Customers.*, Orders.*, [Order Details].*, Products.*
FROM Products
LEFT JOIN ((Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID;

If you copy this one to Nwind, you'll see that both the Customer and
Products table are instructed to show ALL. They are both the 'Left' side of
the outer join. Access can't handle this. It can either show ALL customers
or ALL products, but it can't handle this Many to Many situation.

My suggestion to most query problems is to not try to do it in just one
query. Break it down to 2 or 3 queries. Write data to temp tables if you
have to. Trying to solve using only one query typically does not work, or
is impossible to debug. $0.02

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Did not get any response to my third level reply, so I thought I'd try
posting anew. Thanks!
_________________________________
Here is the SQL view as you and John Vinson directed:

SELECT [Civilian Payroll].[Employee Name], Round(([CY1 End Date]-[FY Start
Date])/365,2)*[Salary] AS SalaryCY1, Round(([FY End Date]-[CY2 Start
Date])/365,2)*[Salary]*(1+[COLA]) AS SalaryCY2,
(([SalaryCY1]+[SalaryCY2]))+(([SalaryCY1]+[SalaryCY2])*[BenefitsPercent])+[Bonus]
AS TotalSalaryBenefitsBonus, CivilianOvertimeCost.OTcost AS Expr1
FROM [Standard Variables], [Civilian Payroll] LEFT JOIN
CivilianOTCostOutputTable ON [Civilian Payroll].[Employee Name] =
CivilianOTCostOutputTable.EmployeeName;

Thanks for helping me out!

Post your SQL.
Open the query in design view and click on menu VIEW - SQL View. Copy
and
paste in post.