[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