Outer Join possible in Access 2002?

[deko]

From what I understand, an Inner Join narrow the rows selected to the table
with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002 will
not allow?

[Peter Russell]

Outer joins are more involved in that there are two types (Left Outer and
Right Outer).

Yes your syntax is wrong.

I can't tell what you are trying to do.

Try building your query in the Access query builder first and looking at
the SQL that it creates. But you need to look at Help for 'Join Types',
first.
Double clicking the join line in the query grid will show the Join
options, but there are restrictions on what will work with what when you
have multiple joins.

Regards

Peter Russell


deko previously wrote:

From what I understand, an Inner Join narrow the rows selected to the
table
with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002
will
not allow?

[Ross Presser]

pi********@hotm ail.com (Pieter Linden) wrote in
news:bf******** *************** ***@posting.goo gle.com:

Access won't do the FULL OUTER JOIN that (I think) exists in Oracle.
You can do LEFT/RIGHT JOINs though. If you want to see the Access
syntax, create a query in the QBE grid with two related tables.
Right-Click on the join line and select either option 2 or 3 (both
outer joins). But those are LEFT or RIGHT, not FULL. I think to get
what you want, you may need to create the LEFT and RIGHT joins and
then UNION them together...

You'll end up with dupes that way, unless you filter them:

SELECT tblA.AField1, tblB.BField1 FROM tblA LEFT JOIN tblB ON tblA.ID = tblB.ID
UNION
SELECT tblA.AField1, tblB.BField1 FROM tblA RIGHTJOIN tblB ON tblB.ID = tblA.ID
WHERE tblA.ID IS NULL

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos

[deko]

Thanks everyone for the responses - much appreciated!

I've discovered that full outer joins are not supported in an access .mdb
(see:
http://office.microsoft.com/assistan...EC010553071033)

However, I was successful in creating the following, which solves my
problem:

SELECT tblTxAcct.TxAcc t_ID, qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al,
qryTxToQ3.Q3Tot al, qryTxToQ4.Q4Tot al, qryTxToYr.YrTot al,
tblTxAcct.TxAcc tName
FROM ((((tblTxAcct LEFT JOIN qryTxToQ1 ON tblTxAcct.TxAcc t_ID =
qryTxToQ1.TxAcc t_ID) LEFT JOIN qryTxToQ2 ON tblTxAcct.TxAcc t_ID =
qryTxToQ2.TxAcc t_ID) LEFT JOIN qryTxToQ3 ON tblTxAcct.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID) LEFT JOIN qryTxToQ4 ON tblTxAcct.TxAcc t_ID =
qryTxToQ4.TxAcc t_ID) LEFT JOIN qryTxToYr ON tblTxAcct.TxAcc t_ID =
qryTxToYr.TxAcc t_ID;

I think I can use something like Nz(Q1Total,0) in the data sheet to populate
blank cells with zeros... but have not yet tried this...


"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...

"deko" <dj****@hotmail .com> wrote in message

news:<WC******* *********@newss vr29.news.prodi gy.com>...

From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002 will not allow?

Access won't do the FULL OUTER JOIN that (I think) exists in Oracle.
You can do LEFT/RIGHT JOINs though. If you want to see the Access
syntax, create a query in the QBE grid with two related tables.
Right-Click on the join line and select either option 2 or 3 (both
outer joins). But those are LEFT or RIGHT, not FULL. I think to get
what you want, you may need to create the LEFT and RIGHT joins and
then UNION them together...

Okay, that's enough "thinking" for one day...