[AccessD] NOT Query.

Heenan, Lambert Lambert.Heenan at aiuholdings.com
Tue Mar 31 10:34:55 CDT 2009


The query is not testing to see if there is a QueryA.Clients with a Null value. It is checking if there are any queryB clients that have no corresponding records in QueryA.  The left join will result in Null being returned as the value of missing QueryA.Clients. So this SQL will return rows containing QueryB.Clients that are not found in QueryA, which was the goal.

The SQL should perhaps be modified to

SELECT QueryB.Clients
FROM QueryB LEFT JOIN QueryA ON QueryB.Clients = QueryA.Clients WHERE
(((QueryA.Clients) Is Null));

As there seems to be little point in displaying the Null value that is the reason why any row will appear in the result set. We know it's null so why show it?

This is exactly the query that the Access "Find Unmatched Query Wizard" will generate for you, and it is considerably faster than using a NOT IN sub query with large data sets. So Rusty was right on the money.


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, March 31, 2009 10:10 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] NOT Query.

If you're sure Clients is actually null... what happens if they're just empty?

<groan> :)

Susan H.


> How about another query with a left join from query B to query A, and pull
> only the records where    queryA.Clients is Null, ie:
>
> SELECT QueryB.Clients, QueryA.Clients
> FROM QueryB LEFT JOIN QueryA ON QueryB.Clients = QueryA.Clients WHERE
> (((QueryA.Clients) Is Null));
>
> HTH
>
> Rusty
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan
> Harkins
> Sent: Monday, March 30, 2009 5:33 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] NOT Query.
>
> EXISTS?
>
> http://www.devx.com/dbzone/Article/9570/1954
>
> Susan H.
>
>
>> Thanks Max - And i was just looking at (NOT IN) and thinking  "oooh,
>> that looks useful" :)  noted!.
>>
>>
>> Darryl,
>> Do not use the NOT IN clause if you can avoid it.  It is extremely
>> slow on large datasets.  The quickest way was given to me (I think by
>> Jim) some time back but I cannot recall it and don't have time to go
>> searching right at this moment.
>>
>> I have two queries.  Query A and Query B both contain a field called
>> Clients (Based on the same underlying data).  A and B correctly
>> return a different number of records in the client field.  I want to
>> be able to list which clients are in query B that are missing in Query A.
>> Basically I want the opposite of joining the two queries based on the
>> clientfield.
>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> **********************************************************************
> WARNING: All e-mail sent to and from this address will be received,
> scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
> corporate e-mail system and is subject to archival, monitoring or
> review by, and/or disclosure to, someone other than the recipient.
> **********************************************************************
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list