Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 720
Bryan Avatar asked Feb 05 '10 22:02

Bryan


People also ask

How do you say not equal in Access query?

<> Operator (Not equal to) <= Operator (Less than or equal to)

How can you write not equal to in the WHERE clause?

The SQL not equal operator is <>. You should specify this in a WHERE statement. This lets you select rows where a particular column's contents is not equal to the value you have specified. You can also use !=

How do you enter no value in Access?

When you need to insert a null value into a column in an Access database, you could use "" to indicate a zero-length string. However, a zero-length string is not the same as Null. To insert a Null value, simple pass the keyword Null for the column, as shown in the code example.

How do you use arithmetic operations in MS Access?

Arithmetic operatorsMultiply two numbers. Divide the first number by the second number. Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer. Divide the first number by the second number, and then return only the remainder.


2 Answers

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.

like image 74
Fionnuala Avatar answered Sep 29 '22 20:09

Fionnuala


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
like image 37
RBarryYoung Avatar answered Sep 29 '22 21:09

RBarryYoung