Keep only unmatched records when joining tables

I have 2 access tables that contain partially similar data, one being more enriched than the other. The idea here is to join the two tables by the fields id and num and to get from the table T2 the num that are not in the table T1

T1:

id	num
1	34
3	51
7	23

T2:

id	num	status
1	34	done
1	79	done
1	39	done
3	51	done
7	23	done

Expected result:

id	num	status
1	79	done
1	39	done

Under access I read on internet that there is no MINUS operator like under MySQL, so I tried with EXCEPT but the query takes a long time (stopped after 10min)

So I tried this:

SELECT T2.*
FROM T2 LEFT JOIN T1 ON (T1.id =T2.id) 
WHERE T1.num IS NULL AND ( (T2.status LIKE 'done') );

The problem now is, I don't have all the records that are in T2 but not in T1

Solution

You can use RIGHT JOIN. And I recommend to do not use "LIKE" in this case because this is slow. You can just use the = operator. So your query will be:

SELECT t2.id, t2.num, t2.status 
FROM t1 
RIGHT JOIN t2 
ON t1.id = t2.id
AND t1.num = t2.num
WHERE t1.num IS NULL
AND t2.status = 'done';

In case all column names you want to join are identic in both tables, you can join more simple:

SELECT t2.id, t2.num, t2.status 
FROM t1 
RIGHT JOIN t2 
USING (id,num)
WHERE t1.num IS NULL
AND t2.status = 'done';

I don't like this, but it's shorter. At the end, your concrete query depends on your personal "taste".