How do I correctly use Not Equal in MS Access

Question

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  lt  gt  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  lt   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

User · Answer

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.

User · Answer

Like this  SELECT DISTINCT Table1 Column1 FROM Table1 WHERE NOT EXISTS  SELECT   FROM Table2     WHERE Table1 Column1   Table2 Column1      You want NOT EXISTS  not  Not Equal     By the way  you rarely want to write a FROM clause like this   FROM Table1  Table2   as this means  FROM all combinations of every row in Table1 with every row in Table2      Usually that s a lot more result rows than you ever want to see   And in the rare case that you really do want to do that  the more accepted syntax is   FROM Table1 CROSS JOIN Table2

User · Answer

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

[sql] How do I correctly use "Not Equal" in MS Access?

Examples related to sql

Examples related to ms-access

Examples related to ms-access-2007