0

In an Access 2013 database, I have a table t1 and another table t2. They both have the same number of columns and column names are also the same. Table t2 have a number of overlaps with id variable of table t1. I am trying to make a new table t3 where I add all the rows of t1 and only those rows of t2 that are not matched by an id variable present in both the tables t1 and t2. I used something like

Create Table t3 As Select * From (Select t1.* From t1 Inner Join t2 on t1.ID_Number = t2. ID_Number)

This throws syntax error. However, even if it worked this will select those rows that matches ID_Number in both the tables. I have tried various other codes and browsed through many other relevant stackoverflow post but could not resolve it.

shammun
  • 177
  • 2
  • 3
  • 14
  • I have also used UNION ALL but this doesn't give the right answer as it copies the overlapping variables also. – shammun Mar 07 '16 at 11:24

2 Answers2

0

try this :

SELECT t1.*
INTO t3
FROM t1
INNER JOIN t2
ON t1.ID_Number = t2.ID_Number
Furkan
  • 415
  • 5
  • 17
  • Thanks. But sorry that I asked it in a wrong way. Actually, I want to select those rows from t2 whose ID does not match with t1. How can I do that? Again, thanks for your answer. – shammun Mar 07 '16 at 10:55
  • Do you want insert dont match data to table t3? Did I understand right – Furkan Mar 07 '16 at 11:01
  • Yes. All rows of t1 and only those rows of t2 that don't match by id. Sorry for confusion. – shammun Mar 07 '16 at 11:04
  • change this : ON t1.ID_Number <> t2.ID_Number – Furkan Mar 07 '16 at 11:05
  • Doesn't work. Creates the same observation a couple of times. Again, if we are putting <> in INNER JOIN it is not even an inner join at all. t1 have 140 observations, t2 has 147 observations, but after I use INNER JOIn with <> I get t3 with 20578 observations!!!! However, I know that there are only 5 to 7 overlaps. So, excluding those overlaps I am expecting the result to be a table of around 282 to 287 observations. – shammun Mar 07 '16 at 11:08
0

I am not sure about Access syntax but can this 2-step solution work?

select t1.* into t3 from t1 where t1.ID_Number not in (select t2.ID_Number from t2)

select t2.* into t3 from t2 where t2.ID_Number not in (select t1.ID_Number from t1)
tomasb
  • 1,663
  • 2
  • 22
  • 29
  • When I try to run the second line, I get an error: "The existing table t3 will be deleted before you run the query" – shammun Mar 07 '16 at 19:26
  • OK then see http://stackoverflow.com/a/4101761/881375 and change second statement – tomasb Mar 08 '16 at 01:28