Access Query Join - Value in One Table Not in Other Table. 3

[kjv1611]

Ok, this is probably going to sound dumb, and be a very easy answer. I just can't remember, but it seems there is a simple way to accomplish this.

Access 2003 Database - very small.

I have 2 tables, each with one field that I wanted to compare.

For the idea, not much informations needed, but here is what I have:
1 database
2 tables
1 field in each table (this is not a production database, just trying to test some data real quick).
The field in each database should be fairly similar - in other words, about half of the records in one table will be identical to the records in the other table.

What I want to do is a join or some other calculation that shows of tables A and B, what accounts are in table A, but not in table B. So, an example would be:

Table A Table B
Apple Apple
Orange Orange
Bannana Bannana
Cherry Grape
Grape

So, the query I want would return one value in this example: Cherry.

If anyone could help me out with this one, I'd greatly appreciate it.

Thanks!

 

[adalger]

The Find Unmatched Query Wizard will do this for you.

 

[gmmastros]

Select TableA.*
From   TableA Left Join TableB On TableA.Field = TableB.Field
Where  TableB.Field Is NULL

The trick here is the left join. With a Left Join, all records from the Left side of the join will be returned. If there is a match to the table on the right side, the data will be returned. If there isn't a match, then you will still get a record, but the value in the fields from the right side of the join will have NULL in all the fields.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kjv1611]

Wow! That's it! Of course, it's one of those DUH! moments. That's one I've used, but couldn't remember b/c I've only used it once or maybe twice in all my doings so far.

Thanks, adalger!

 

[PHV]

A more complete answer:
SELECT A.ID, 'Only in A' AS Reason FROM TableA AS A LEFT JOIN TableB AS B ON A.ID=B.ID WHERE B.ID Is Null
UNION SELECT B.ID, 'Only in B' FROM TableA AS A RIGHT JOIN TableB AS B ON A.ID=B.ID WHERE A.ID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

And thanks for the SQL, there, gmmastros.

This is what I ended up with (SQL view of the query):

SELECT TableA.Field1
FROM TableA LEFT JOIN TableB ON TableA.Field1 = TableB.Field2
WHERE (((TableB.Field2) Is Null));

 

[kjv1611]

PHV,

So what your query would do is to select all records from either table where it only exists in one of the 2 tables? I'll have to try that just to see, as that may be of some use, for sure.

Thanks!

 

[kjv1611]

I tried your suggestion, just now, PHV, and that was a really good idea, so that I could pull all possibilities - either/or in for review. I don't think I'll need that for the current situation, but it is sure to be useful in the not too distant future.

Thanks again!