[AccessD] In this Query but NOT that Query

Hi Art,

There is joy in the world! (at least in my world!) :)

I've never used subqueries before and I've never seen the NOT IN syntax.
Seems really useful.

No, there were no duplicates in either list.  With this example, the most I
could expect back would be 8 if all the 13 were different that those first
8.  

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Sunday, March 25, 2007 2:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] In this Query but NOT that Query

If the outer join doesn't work and the NOT IN() syntax doesn't work, I think
the only possibility left is that the data types do not match in the
underlying tables. Perhaps one is larger than the other.

SELECT PeopleID FROM queryA WHERE PeopleID NOT IN( SELECT PeopleID FROM
queryA ).

Incidentally, how is it that one query contains 13 rows while the other
contains 5 rows, and you're expecting only 3 rows back? Are there
duplicates?

A.



----- Original Message ----
From: Dan Waters <dwaters at usinternet.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Sunday, March 25, 2007 3:29:15 PM
Subject: Re: [AccessD] In this Query but NOT that Query


Hi Eric,

I carefully set this up, but no joy.  This looks like the No Match query
template - I think this would work if it was two tables that were related.

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Sunday, March 25, 2007 12:59 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] In this Query but NOT that Query

SELECT a.*
FROM qryA a 
    LEFT OUTER JOIN qryB b
    ON b.PeopleID = a.PeopleID
WHERE (b.PeopleID IS NULL) 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, March 25, 2007 10:39 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] In this Query but NOT that Query

Queries!  They hurt my hair!


I have a qryA which has one field PeopleID.  It has 8 Distinct rows.

I also have qryB which has one field PeopleID.  It has 13 Distinct rows.  In
qry B there are 5 rows where PeopleID is the same as in qryA.

I need to write a query (or queries) which gives me a list of the three
records that are in qryA but not in qryB.  

Any suggestions?

Thanks!
Dan


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

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.17/732 - Release Date: 3/24/2007
4:36 PM


-- 
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com