Prev: Delete Records Where The Like* Can Be used??
Next: Querying Date Field, Disregarding Year
From: John Spencer on 24 Mar 2010 11:47
The problem is given two tables with a multi-field OUTER join.

Drag from tableA Field1 to TableB Field 1 and set the join (all records in A
and matching in B) (LEFT JOIN)

Drag from tableB Field@ to TableA Field @ and set the join (left Join) (all
records in A and matching in B) (RIGHT JOIN)

This means you have ambiguous joins between the tables. The Arrows in the
graphical interface will both point from TableA to TableB.

Whether or not this is a bug depends on your point of view. The obvious thing
to do is to always drag in the same direction when setting up a multi-field
outer join.

In Access an Inner Join will work (although the On clause looks a little
weird). Not the reversal of tables in the following SQL statement.

SELECT *
FROM TableA INNER JOIN TableB
ON TableA.Field1 = TableB.Field1
AND TableB.Field2 = TableA.Field2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

vanderghast wrote:
> Yep, given two 'lines' between tableA and tableB, both lines must be
> of the same type, ie, both without arrows (inner join), or both with an
> arrow pointing toward the same table. While it does not make sense to
> have two lines with arrows each pointing to different table, in this
> scenario, since which table would get all its records preserved,
> returned, in the result?
>
> A fourth possibility, a full outer join, is generally pictured (when
> supported) by two arrows, one at each end of the line representation of
> the join. ***IF*** this is what you try to do, full outer join are not
> directly supported by Jet, while they are with MS SQL Server, so for
> Jet, you have to use one of the possible works around.
>
>
> Vanderghast, Access MVP
>
>
>
> "Ivan" <ivan(a)nekje.si> wrote in message
> news:%23tXzS$0yKHA.404(a)TK2MSFTNGP02.phx.gbl...
>> I fear you didn't understand me.
>>
>> I have spoken only about two tables, where there are two or more outer
>> joins (arrows) between these two tables.
>>
>> You can create these outer joins (equally oriented arrows) graphicaly
>> using mouse. And all these arrows must be created starting from the
>> same table. Namely although you can in principle build graphicaly the
>> equally oriented arrow starting from the opposite table and declaring
>> orientation of the join in Join Properties window, you get after such
>> action but the error message.
>>
>> Ivan
>>
>>
>> "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message
>> news:19C6BD6B-9D0F-41AF-8449-9C6948D4340E(a)microsoft.com...
>>> If I understand you, this is GUI of your query --
>>>
>>> Table1 arrow
>>> Table3
>>> Table2 arrow
>>>
>>> I assume it will not know which happens first.
>>>
>>> Try using a union query like this --
>>>
>>> Table1 arrow Table1 arrow Table3
>>> union
>>> Table2 arrow Table2 arrow Table3_1
>>>
>>> Left join union to Table1 left join to Table3
>>> Left join union to Table2 left join to second instance of Table3
>>>
>>> Hope you follow this. If not then post back and I will do the SQL.
>>>
>>> --
>>> Build a little, test a little.
>>>
>>>
>>> "Ivan" wrote:
>>>
>>>> Hello,
>>>>
>>>> in GUI of Access 2007 (12.0.6423.1000) SP2 MSO (12.0.6521.5000) is
>>>> generally very simple to make a select query between two tables
>>>> joined with
>>>> two or more outer joins (of the same kind normally).
>>>>
>>>> But you should be careful. If you build first join clicking on a
>>>> field of
>>>> the first table, drag then the connection to the field of the second
>>>> table
>>>> and choose the right outer join, then you have to make the next
>>>> outer join
>>>> between the same two tables on the same way. If you begin but with the
>>>> second table first and although you choose the same type of outer
>>>> join (the
>>>> arrows are oriented to the same table) you get the error message:
>>>> "The SQL
>>>> statement could not be executed because it contains ambiguous outer
>>>> join. To
>>>> force the one of the joins to be perormed first, create a separate
>>>> query
>>>> that performs the first join and then include that query in your SQL
>>>> statement." if you try to run the query.
>>>>
>>>> You could not see even the SQL View. If you try to change the view
>>>> you get
>>>> the same error message!
>>>>
>>>> Is there anyone with the same experinece?
>>>>
>>>> Greeting
>>>>
>>>> Ivan
>>>>
>>>>
>>>> .
>>>>
>>
>>
>
 | 
Pages: 1
Prev: Delete Records Where The Like* Can Be used??
Next: Querying Date Field, Disregarding Year