Objective:
The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table.
Current Query:
SELECT DISTINCT Table1.Column1
FROM Table2, Table1
WHERE Table1.Column1 <> Table2.Column1
Results From Query:
What happens when I try to run this query is the progress bar fills up almost immediately but then it pretty much freezes and doesn't do anything else as far as I can see. When I use an = sign instead of <> it outputs the values that are equal just fine and if I replace Table2.Column1 with an actual actual value it works just fine.
I just ran it again while typing this question and the above query gave me an answer this time but it has all of the DISTINCT values for the column not all of the values unique to just that table like it should.
Any ideas on what I'm doing wrong or missing here?
This question is related to
sql
ms-access
ms-access-2007
In Access, you will probably find a Join is quicker unless your tables are very small:
SELECT DISTINCT Table1.Column1
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 Is Null
This will exclude from the list all records with a match in Table2.
I have struggled to get a query to return fields from Table 1 that do not exist in Table 2 and tried most of the answers above until I found a very simple way to obtain the results that I wanted.
I set the join properties between table 1 and table 2 to the third setting (3) (All fields from Table 1 and only those records from Table 2 where the joined fields are equal) and placed a Is Null in the criteria field of the query in Table 2 in the field that I was testing for. It works perfectly.
Thanks to all above though.
Source: Stackoverflow.com