By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

Problem with "Not In" Subquery

P: 3
I have a problem with using a subquery in MS Access. The purpose of the sub-query is to create a list of people who have had doctor exams in the past 6 months and exclude them from the main query. This is what I came up with:

Expand|Select|Wrap|Line Numbers
  1. SELECT GI.generalinfoid AS GeneralInfoId, max(GI.FirstName) AS FirstName, max(GI.LastName) AS LastName, max(TI.ImagingDate) AS ImagingDate, Max(EM.embolizationDate) AS embolizationDate
  2.  
  3. FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
  4.  
  5. WHERE ((GI.name) Is Not Null Or (GI.name)<>"" Or (GI.Lastname) Is Not Null Or (GI.Lastname)<>"") And (GI.generalinfoid)<>14598 And (GI.active) Like "Yes" And (GI.EMBCOUNT)>=1
  6.  
  7.  
  8. and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)
  9.  
  10.  
  11. GROUP BY GI.generalinfoid
  12. ORDER BY max(GI.LastName);

The above query does not cause an error but it does not return any data. If I take out the subquery part, it returns over a thousand rows. If I replace "not in" with "in" in the subquery it returns about 65 rows. What can I do to recover the missing 900+ rows with the "not in" subquery?

Incidentally, I tried the following solution but it errored out my Access program:

Expand|Select|Wrap|Line Numbers
  1. SELECT GI.generalinfoid AS GeneralInfoId, max(GI.FirstName) AS FirstName, max(GI.LastName) AS LastName, max(TI.ImagingDate) AS ImagingDate, Max(EM.embolizationDate) AS embolizationDate
  2.  
  3.  
  4. FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
  5.  
  6.  
  7. WHERE NOT EXISTS
  8.  
  9. (select distinct GeneralInfoID from tblImaging TI where TI.generalInfoID = GI.GeneralInfoID AND DateDiff("m",(TI.ImagingDate),Now())<=6)
  10.  
  11. AND
  12.  
  13. ((GI.name) Is Not Null Or (GI.name)<>"" Or (GI.Lastname) Is Not Null Or (GI.Lastname)<>"") And (GI.active) Like "Yes" And (GI.EMBCOUNT)>=1
  14.  
  15.  
  16. GROUP BY GI.generalinfoid
  17. ORDER BY max(GI.LastName);

Anyways, any help would be greatly appreciated. Thanks in advance.
May 23 '07 #1
Share this Question
Share on Google+
13 Replies


code green
Expert 100+
P: 1,726
How many rows does the subquery return?
Expand|Select|Wrap|Line Numbers
  1. select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6
May 24 '07 #2

P: 3
The subquery returns 65 Rows
May 24 '07 #3

Rabbit
Expert Mod 10K+
P: 12,364
Try using a different alias for the subquery. Right now the main query's TI alias overlaps with the subquery's TI, that may be the problem. Or leave off the subquery's alias altogether.
May 24 '07 #4

P: 3
Tried this...Gave me the same result of no rows

Expand|Select|Wrap|Line Numbers
  1. SELECT GI.generalinfoid AS GeneralInfoId, max(GI.FirstName) AS FirstName, max(GI.LastName) AS LastName, max(TI.ImagingDate) AS ImagingDate, Max(EM.embolizationDate) AS embolizationDate
  2.  
  3. FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
  4.  
  5. WHERE ((GI.name) Is Not Null Or (GI.name)<>"" Or (GI.Lastname) Is Not Null Or (GI.Lastname)<>"") And (GI.generalinfoid)<>14598 And (GI.active) Like "Yes" And (GI.EMBCOUNT)>=1
  6.  
  7.  
  8. and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging tbm where DateDiff("m",(tbm.ImagingDate),Now())<=6)
  9.  
  10.  
  11. GROUP BY GI.generalinfoid
  12. ORDER BY max(GI.LastName);
May 25 '07 #5

NeoPa
Expert Mod 15k+
P: 31,473
It seems to me that your original line 8
Expand|Select|Wrap|Line Numbers
  1. and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)
is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. AND DateAdd('m',6,TI.ImagingDate)>=Date()
Would that not be a little more straightforward?
May 25 '07 #6

Rabbit
Expert Mod 10K+
P: 12,364
It seems to me that your original line 8
Expand|Select|Wrap|Line Numbers
  1. and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)
is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. AND DateAdd('m',6,TI.ImagingDate)>=Date()
Would that not be a little more straightforward?
Good catch, totally missed that. I should concern myself more with what the query is trying to achieve.

But a not on aliases. If you use an alias, then every field from that table has to be prefixed with alias.
May 25 '07 #7

NeoPa
Expert Mod 15k+
P: 31,473
But a not on aliases. If you use an alias, then every field from that table has to be prefixed with alias.
Hi PW.
What did you mean by your second paragraph?
May 25 '07 #8

Rabbit
Expert Mod 10K+
P: 12,364
Hi PW.
What did you mean by your second paragraph?
I don't know...

lol, What I meant was:

On a side note. If you use an alias, then every field from that table has to be prefixed with alias.
May 25 '07 #9

NeoPa
Expert Mod 15k+
P: 31,473
A bit like the following? :
Expand|Select|Wrap|Line Numbers
  1. SELECT PW.Field1,PW.Field2
  2. FROM PeskyWabbit AS PW
Seriously, I didn't find that to be necessarily true in my testing.
Expand|Select|Wrap|Line Numbers
  1. SELECT PW.Field1,Field2
  2. FROM PeskyWabbit AS PW
worked fine for me (Strangely, I didn't use these exact terms :D).
May 25 '07 #10

Rabbit
Expert Mod 10K+
P: 12,364
A bit like the following? :
Expand|Select|Wrap|Line Numbers
  1. SELECT PW.Field1,PW.Field2
  2. FROM PeskyWabbit AS PW
Yeah, he did that in his main query but not in his subquery.
May 25 '07 #11

NeoPa
Expert Mod 15k+
P: 31,473
Sorry PW, I retro edited my last post to avoid accusations of post whoring :embarassed:

Seriously, I didn't find that to be necessarily true in my testing.

Expand|Select|Wrap|Line Numbers
  1. SELECT PW.Field1,Field2
  2. FROM PeskyWabbit AS PW
worked fine for me (Strangely, I didn't use these exact terms :D).
May 25 '07 #12

Rabbit
Expert Mod 10K+
P: 12,364
Hmm, perhaps not. I don't know where I got the idea from.
May 25 '07 #13

NeoPa
Expert Mod 15k+
P: 31,473
I would guess from one of those clever self-linking sub-queries you're so good at ;) There it would certainly make sense.
May 25 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.