Relationship - Outer Join - Microsoft Access 2002

[Vanessa Simmonds]

Good Morning,

I have a queries which for the moment contains two tables (DUIT and NPI) and
one outer join (all Values on DUIT and one value from NPI).

I now want to included a third table (actions) and create another outer join
(all values on DUIT and one value from NPI).

However Access is telling me that "Only two tables are allowed with an outer
join".

How can i make this work?

Best Regards,

Vanessa Simmonds

 

[John Spencer]

You should be able to have multiple outer joins in a query. BUT in some cases
the differing outer joins cannot be resolved. In that case you might need to
use nested queries.

Query one would be your current query. Create a new query and add query one
and the additional table to the new query and set up your join based on this.

If you can post the SQL of the query that will not work perhaps someone can
help you rewrite it.

This should work
SELECT *
FROM (DUIT LEFT JOIN NPI
ON DUIT.xxx = NPI.yyy)
LEFT JOIN Actions
ON DUIT.aaa = Actions.bbb

This **might** fail (note the direction of the outer joins has changed)
SELECT *
FROM (NPI RIGHT JOIN DUIT
ON DUIT.xxx = NPI.yyy)
LEFT JOIN Actions
ON DUIT.aaa = Actions.bbb


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