Re: Cartesian product & outer join

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
Consulting@xxxxxxxxxx
www.fmsinc.com/consulting

"Ann Scharpf" <AnnScharpf@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B349F88C-F8A6-47E4-ABAC-C697E921181A@xxxxxxxxxxxxxxxx
> 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!
>
>
> "KARL DEWEY" wrote:
>
>> Post your SQL.
>> Open the query in design view and click on menu VIEW - SQL View. Copy
>> and
>> paste in post.
>>
>> "Ann Scharpf" wrote:
>>
>> > I am creating my first database, so I might be doing things
>> > incorrectly/inefficiently here ... but this, in part, is what I've
>> > done.
>> > Pertinent to this question are three tables and a query. I am working
>> > at a
>> > goverment facility and am calculating salary expense data for a fiscal
>> > year
>> > (crossing 2 calendar years with different pay rates.)
>> >
>> > 1. Table:Civilian payroll. 57 records. Salary level info & FY
>> > BEGIN/END
>> > dates for each person (to accomodate mid-year hires & terminations.)
>> > Has
>> > benefits rate, COLA etc.
>> > 2. Table:Standard variables (COLA, Benefits rate etc)
>> > 3. Table:Raw time*** data, imported from time system
>> > 4. Query:Civilian overtime cost. Query is agains time*** data.
>> > Totals
>> > time with overtime time categories and multiplies by civilian overtime
>> > rate.
>> > (Same is used for all employees.) Results table has 4 rows.
>> >
>> > I am now trying to create a second query. The query worked fine when
>> > it
>> > just used the first two tables (payroll & standard variables). Worked
>> > like a
>> > gem for calculating the salary for each part of the year. Adds up
>> > correctly
>> > and everything.
>> >
>> > Now I want to add the overtime values for the 4 people who have
>> > overtime.
>> > When I add the overtime cost query to the current query, I get 228
>> > rows. So
>> > I looked in my handy Access book and thought a left outer join would
>> > fix my
>> > problem. (Payroll table on left, OT query on right). But when I try
>> > to run
>> > the query, I get an error that says:
>> >
>> > 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.
>> >
>> > How do I get Access to do this? Thanks very much for any help you can
>> > give
>> > me.
>> >
>


.