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.TxAcct_ID, qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,
qryTxToQ3.Q3Total, qryTxToQ4.Q4Total, qryTxToYr.YrTotal,
tblTxAcct.TxAcctName
FROM ((((tblTxAcct LEFT JOIN qryTxToQ1 ON tblTxAcct.TxAcct_ID =
qryTxToQ1.TxAcct_ID) LEFT JOIN qryTxToQ2 ON tblTxAcct.TxAcct_ID =
qryTxToQ2.TxAcct_ID) LEFT JOIN qryTxToQ3 ON tblTxAcct.TxAcct_ID =
qryTxToQ3.TxAcct_ID) LEFT JOIN qryTxToQ4 ON tblTxAcct.TxAcct_ID =
qryTxToQ4.TxAcct_ID) LEFT JOIN qryTxToYr ON tblTxAcct.TxAcct_ID =
qryTxToYr.TxAcct_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********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"deko" <dj****@hotmail.com> wrote in message
news:<WC****************@newssvr29.news.prodigy.co m>...
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.Q1Total, qryTxToQ2.Q2Total, qryTxToQ3.Q3Total,
qryTxToQ4.Q4Total
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON
qryTxToQ3.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON qryTxToQ4.TxAcct_ID =
qryTxToQ3.TxAcct_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...