Trouble with 'Not In'

  • Thread starter Thread starter Michael Rossi
  • Start date Start date
M

Michael Rossi

I am trying to write what seems to be a relatively simple query in Access but
I am having problems with it and cannot find a solution anywhere. I even
asked the head of our IT dept who is quite proficient with Access and he was
stumped as well. Here is the background:

I have 2 tables. One table is called 'Associates' and holds the names, ID
numbers, hire date, and shift # of people in the company. The other is
called [Process Audits All] and keeps track of the employees that have been
audited. What the query is supposed to do is just give me a list of the
associates in the company who have NOT yet been audited. The idea is to have
every associate audited within 60 days of their hire date. The code I wrote
is as follows:

SELECT Associates.EmpNum, Associates.Name, Associates.[Hire Date]+60 AS
[Audit By]
FROM Associates
WHERE associates.EmpNum not in (select distinct [process audits
all].AuditeeIDNum from [process audits all]);

The result of this is no data.

For some reason, when I use "In" instead of "Not In", I get the opposite of
what I'm looking for (meaning I get the list of associates who HAVE been
audited). This leaves me to believe that there is something specific to the
"Not In" clause. I even tried to do an inner join but that didn't seem to
work either. If someone can please help me out with this I would greatly
appreciate it.

Thank you,
Michael Rossi
 
WHERE ... NOT IN (SELECT fieldName FROM somewhere )

returns no record if the sub-query returns a NULL.


Try:


WHERE ... NOT IN (SELECT fieldName FROM somewhere WHERE NOT (fieldName IS
NULL))




Vanderghast, Access MVP


--------------------
ps:

a NOT IN( b, c, d)

is the same as

a <> b AND a<> c AND a<> d


if, say, d is null, then a <> d returns null:


? 4 <> null
null


and the AND aggregation having a null returns null, or false, but never true

? true AND null
null

? false AND null
false


so,

a NOT IN( b, c, d)

with d null never returns true. And the where clause eliminates each and
every record.


--------------------------
ps ps:


Access behaves differently with a list of constant.

? eval(" 3 NOT IN (1, 2 null) " )
true



The 'problem' only occurs with NOT IN(SELECT ... ). Well, that is not a
'problem', if you are aware of the NULL effect, isn't it? anymore?
 
was missing a coma, should have been, near the end of the second
post-scriptum:


? eval(" 3 NOT IN (1, 2, null) " )
-1
 
That worked. Thank you very much.

Sincerely,
Michael Rossi

Michel Walsh said:
WHERE ... NOT IN (SELECT fieldName FROM somewhere )

returns no record if the sub-query returns a NULL.


Try:


WHERE ... NOT IN (SELECT fieldName FROM somewhere WHERE NOT (fieldName IS
NULL))




Vanderghast, Access MVP


--------------------
ps:

a NOT IN( b, c, d)

is the same as

a <> b AND a<> c AND a<> d


if, say, d is null, then a <> d returns null:


? 4 <> null
null


and the AND aggregation having a null returns null, or false, but never true

? true AND null
null

? false AND null
false


so,

a NOT IN( b, c, d)

with d null never returns true. And the where clause eliminates each and
every record.


--------------------------
ps ps:


Access behaves differently with a list of constant.

? eval(" 3 NOT IN (1, 2 null) " )
true



The 'problem' only occurs with NOT IN(SELECT ... ). Well, that is not a
'problem', if you are aware of the NULL effect, isn't it? anymore?






Michael Rossi said:
I am trying to write what seems to be a relatively simple query in Access
but
I am having problems with it and cannot find a solution anywhere. I even
asked the head of our IT dept who is quite proficient with Access and he
was
stumped as well. Here is the background:

I have 2 tables. One table is called 'Associates' and holds the names, ID
numbers, hire date, and shift # of people in the company. The other is
called [Process Audits All] and keeps track of the employees that have
been
audited. What the query is supposed to do is just give me a list of the
associates in the company who have NOT yet been audited. The idea is to
have
every associate audited within 60 days of their hire date. The code I
wrote
is as follows:

SELECT Associates.EmpNum, Associates.Name, Associates.[Hire Date]+60 AS
[Audit By]
FROM Associates
WHERE associates.EmpNum not in (select distinct [process audits
all].AuditeeIDNum from [process audits all]);

The result of this is no data.

For some reason, when I use "In" instead of "Not In", I get the opposite
of
what I'm looking for (meaning I get the list of associates who HAVE been
audited). This leaves me to believe that there is something specific to
the
"Not In" clause. I even tried to do an inner join but that didn't seem to
work either. If someone can please help me out with this I would greatly
appreciate it.

Thank you,
Michael Rossi
Click to expand...
Click to expand...
 
Back
Top