Query Not Equal

[Confused]

In my query grid I have CLECID and [System ID]

This subquery statement in the criteria part seems to work great. It shows
all of the customers along with their other systems that have system 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID] <>6)

This statement however, shows all of the customers EXCEPT those that ONLY
have System 6.

How do I show only customers that do not have System 6?

 

[Dean]

Can you add [CLEC Systems3] to your QBE design, inner join them on CLECID,
and add a criteria to CLECID as <> 6?

 

[Confused]

Correction:

In my query grid I have CLECID and [System ID]

This subquery statement in the criteria part seems to work great. It shows
all of the customers along with their other systems that have system 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID] =6)

This statement however, shows all of the customers EXCEPT those that ONLY
have System 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID]

 

[Confused]

[CLEC Systems3] is the only table that is part of the query grid. It has
CLECID and System ID. I need it to show the records for customers who do not
have <>6. It seems to work fine when I run the query with = 6. But results
I wouldn't expect for <> 6, as mentioned - This statement however, shows
all of the customers EXCEPT those that ONLY
have System 6. I need it to show all customers who do not have system
6.

Thanks!

Can you add [CLEC Systems3] to your QBE design, inner join them on CLECID,
and add a criteria to CLECID as <> 6?

In my query grid I have CLECID and [System ID]

This subquery statement in the criteria part seems to work great. It shows
all of the customers along with their other systems that have system 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID] <>6)

This statement however, shows all of the customers EXCEPT those that ONLY
have System 6.

How do I show only customers that do not have System 6?

 

[Dean]

For your criteria, try

[System ID] <> 6
AND [System ID] IS NOT NULL
AND IsNumeric([System ID]) = True

What data type is [System ID]?

[CLEC Systems3] is the only table that is part of the query grid. It has
CLECID and System ID. I need it to show the records for customers who do not
have <>6. It seems to work fine when I run the query with = 6. But results
I wouldn't expect for <> 6, as mentioned - This statement however, shows
all of the customers EXCEPT those that ONLY
have System 6. I need it to show all customers who do not have system
6.

Thanks!

Can you add [CLEC Systems3] to your QBE design, inner join them on CLECID,
and add a criteria to CLECID as <> 6?

In my query grid I have CLECID and [System ID]

This subquery statement in the criteria part seems to work great. It shows
all of the customers along with their other systems that have system 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID] <>6)

This statement however, shows all of the customers EXCEPT those that ONLY
have System 6.

How do I show only customers that do not have System 6?

 

[John Spencer]

SELECT *
FROM [Clec Systems3]
WHERE [ClecID] NOT IN
(SELECT [ClecID]
FROM [Clec Systems3]
WHERE [System Id] = 6)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Confused]

That's about as quick as you can go from difficult to easy. I got a good
laugh out of that one! Thanks.

Not In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID]
= 6)

Ken Sheridan
Stafford, England

Correction:

In my query grid I have CLECID and [System ID]

[quoted text clipped - 5 lines]

This statement however, shows all of the customers EXCEPT those that ONLY
have System 6.

In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID]

How do I show only customers that do not have System 6?

 

[James A. Fortune]

One caveat: The NOT IN predicate will not work if the subquery returns a
NULL in any of the rows. I can't see that being the case here, but in
situations where that is a possibility the NOT EXISTS predicate should be
used and the subquery correlated with the outer query.

Ken Sheridan
Stafford, England

Not In (Select CLECID From [CLEC Systems3] Where [CLEC Systems3].[System ID]
= 6)

Ken Sheridan
Stafford, England

Most queries that use NOT IN can be converted to a faster equivalent
query that uses a so-called frustrated LEFT JOIN. The NOT EXISTS
predicate you mention does not appear to have the same serious
performance limitations that NOT IN has.

See:

http://bytes.com/topic/access/answers/210750-sql-join-nested-query

(Google Group Search is still acting up and the suggested remedy of
using Advanced Search is little better so I can't pull up the original
CDMA post at this time.)

Note: I can probably solve the original question from that post in a
single query now. I was a little wet behind the ears with SQL back then
.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Another factor in the choice between a JOIN and using the EXISTS predicate
against a subquery is whether the query needs to be updatable.

Good point.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

James:

Another point we should perhaps mention for the benefit of any lurkers is
that in the context of the original question in this thread a LEFT OUTER JOIN
would not be possible because a restriction is being placed on a column on
the right side of the join, which in effect would make it an inner join.
Testing for a non-match on the right side ([CLEC Systems3].CLECID IS NULL)
and at the same time for a value in a column on the right side ([CLEC
Systems3].[System ID] = 6) is a logical inconsistency.

Ken Sheridan
Stafford, England

We? It's your point and it's absolutely correct. It's a good thing I
said "Many" and mentioned NOT EXISTS, making sure that a way to back out
gracefully exists . I'll try to resolve the possibilities as much as
possible by referring to NOT IN as a last resort.

James A. Fortune
(e-mail address removed)