Showing unmatched records using a query

[SetBuilder]

How do I write a query in Access that will show what records are on table "A" and not on table "B"?

For example: Table A Table B
1 2
2 3
3 4
4 5
5 6

Results of 1st query (looking from A) shows "1"
Results of 2nd query (looking from B) shows "6"


has records "1","2",3,4,5 Table B has records 2,3,4,5,6. What I want to do is show results of 1 not on table B. I will duplicate the process and look the other way, to give me "6" on B and not on "A"

 

[swampBoogie]

select a.c1
  from a left outer join b
    on a.c1 = b.c1
  where b.c1 is null

 

[SetBuilder]

Thanks. That got it. After looking at your solution, I was directed to another solution that also gave me the same number of rows using a subquery, using the Access "Not Exist" predicate:

SELECT c1
FROM a
WHERE Not Exists
(select * from b
where a.c1 = b.c1);

Thanks again. It is rapid response from people like you that make this website worthwhile.

 

[ClaireHCM]

Though it may come out the same answer,but always consider which way is more optimized.

Using
select a.c1
from a left outer join b
on a.c1 = b.c1
where b.c1 is null

will be more efficient than using "not exists",because you are scaning the whole table.

 

[SetBuilder]

Exactly right! I built two queries, both swampboogies way, and my way. My way took about 3 minutes comparing 11,000 records on table A with about 10,000 records on table B. Swampboogie's way took about 2 seconds, if that.

I was going to send an email stating that, but got busy fixing my code. Thanks to the both of you.