[thelist] Nested Left Outer Join in Access ?

rudy r937 at interlog.com
Fri Aug 17 16:57:38 CDT 2001


> I'm not used to writing joins this way (and barely used to writing them
>  *any* way) - but it's *not* giving me an error in Access.

understand fully

i've seen so many different dialects of sql that i have to look everything
up every time

and msaccess is the worst

>Any further hints?

uh, about what?  you said you aren't getting any errors, so i guess that
means it isn't returning the result set that you think it should?

i have an idea, that actually was prompted by a post by anthony just a few
minutes ago -- use a view

use a view to define the multi-table join where everything is an inner join

now, the trick in msaccess to creating a view is, you create it as a query

write your SELECT with all the INNER joins, and save it as a query, which
becomes your view

then write a second query that selects from the first query with a LEFT
JOIN to the last table

the thing is, if you run the first query by itself, you should see all the
fields of the various tables, and all the rows that will be there in the
final step

then when you run the second query, it will be as if it "appends" columns
from the last table (it cannot add any more rows, right?) -- or else it
will append nulls, since it's a LEFT JOIN

make sense?


rudy





More information about the thelist mailing list