CODEWITHSUNDEEP
sqldatabasems-access
YvetteLee
YvetteLee

Reputation: 1090

MS Access merge two tables

I need to create a new table base on two tables. I have a database in ms access and need to migrate. 

               tableT                                          tableS

  ID   CustID   DATE   Exp1   Exp2              ID   CustID   DATE   Tem1   Tem2
  --------------------------------              ---------------------------------
  1       1    1/1/00   5      5                1       1    1/1/00   3      4
  2       2    1/1/00   1      3                2       2    1/1/00   5      0
  3       1    3/1/00   3      2                3       1    5/1/00   0      3
  4       3    4/1/00   4      1                4       3    6/1/00   0      0

Desired output table tableNew:

  ID   CustID   DATE   Exp1   Exp2  Tem1   Tem2
  ---------------------------------------------
  1       1    1/1/00   5      5     3      4
  2       2    1/1/00   1      3     5      0
  3       1    3/1/00   3      2           
  4       3    4/1/00   4      1     
  5       1    5/1/00                0      3
  6       3    6/1/00                0      0

If I use outer join, I will not get the output I need.

Any idea or help.

Upvotes: 1

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want a full join. You can emulate this in MS Access using:

select t.CustID, t.DATE, t.Exp1, t.Exp2, s.tem1, s.tem2
from TableT as t left outer join
     tableS as s
     on t.CustId = s.CustId and t.date = s.date
union all
select s.CustID, s.DATE, null, null, s.tem1, s.tem2
from tableS as s left outer join
     tableT as t
     on t.CustId = s.CustId and t.date = s.date
where t.CustId is null;

Upvotes: 2

Related Questions