bh***********@mci.com wrote:
Hello Gurus,
I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!
How can I accomplish this in one query? or Can I?
Thanks.
Perhaps this will get you started:
tblCurrent
CID Data1 Data2
1 X Y
2 A B
3 C D
4 G H
tblPrevious
PID Data1 Data2
1 E F
2 G H
qryLeftRightInner:
SELECT tblCurrent.Data1, tblCurrent.Data2, tblCurrent.CID, Null AS PID
FROM tblCurrent LEFT JOIN tblPrevious ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblPrevious.Data2 Is Null UNION SELECT
tblPrevious.Data1, tblPrevious.Data2, Null AS CID, tblPrevious.PID FROM
tblPrevious LEFT JOIN tblCurrent ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblCurrent.Data2 IS NULL UNION SELECT
tblCurrent.Data1, tblCurrent.Data2, tblCurrent.CID, tblPrevious.PID
FROM tblCurrent INNER JOIN tblPrevious ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblCurrent.Data1 = tblPrevious.Data1 AND
tblCurrent.Data2 = tblPrevious.Data2;
! qryLeftRightInner:
Data1 Data2 CID PID
A B 2
C D 3
E F 1
G H 4 2
X Y 1
qryNewTable:
SELECT tblLeftRightInner.Data1, tblLeftRightInner.Data2,
tblLeftRightInner.CID, tblLeftRightInner.PID INTO tblNewTable FROM
tblLeftRightInner;
I don't see yet how to get it down to one query in an elegant way.
Since the union query does its own select distinct I took out the
distinctrow that normally gets inserted by the unmatched query wizard.
The second LEFT JOIN is really a RIGHT JOIN done backwards since using
the same syntax and swapping table names was easier.
James A. Fortune