Problem with "Not In" Subquery

[ThePrinceIsRight]

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:

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
 
FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
 
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
 
 
and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)
 
 
GROUP BY GI.generalinfoid
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:

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
 
 
FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
 
 
WHERE NOT EXISTS
 
(select distinct GeneralInfoID from tblImaging TI where TI.generalInfoID = GI.GeneralInfoID AND DateDiff("m",(TI.ImagingDate),Now())<=6)
 
AND
 
((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
 
 
GROUP BY GI.generalinfoid
ORDER BY max(GI.LastName);

Anyways, any help would be greatly appreciated. Thanks in advance.

[code green]

How many rows does the subquery return?

select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6

[ThePrinceIsRight]

The subquery returns 65 Rows

[Rabbit]

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.

[ThePrinceIsRight]

Tried this...Gave me the same result of no rows

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
 
FROM tblImaging AS TI RIGHT JOIN (qryGeneralInfo AS GI INNER JOIN tblEmbolizations AS EM ON GI.generalInfoID=EM.peopleID) ON TI.GeneralInfoID=GI.generalinfoid
 
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
 
 
and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging tbm where DateDiff("m",(tbm.ImagingDate),Now())<=6)
 
 
GROUP BY GI.generalinfoid
ORDER BY max(GI.LastName);

[NeoPa]

It seems to me that your original line 8

and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)

is equivalent to :

AND DateAdd('m',6,TI.ImagingDate)>=Date()

Would that not be a little more straightforward?

[Rabbit]

It seems to me that your original line 8

and TI.GeneralInfoID not in (select distinct GeneralInfoID from tblImaging TI where DateDiff("m",(TI.ImagingDate),Now())<=6)

is equivalent to :

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.

[NeoPa]

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?

[Rabbit]

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.

[NeoPa]

A bit like the following? :

SELECT PW.Field1,PW.Field2
FROM PeskyWabbit AS PW

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

SELECT PW.Field1,Field2
FROM PeskyWabbit AS PW

worked fine for me (Strangely, I didn't use these exact terms :D).

[Rabbit]

A bit like the following? :

SELECT PW.Field1,PW.Field2
FROM PeskyWabbit AS PW

Yeah, he did that in his main query but not in his subquery.

[NeoPa]

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.

SELECT PW.Field1,Field2
FROM PeskyWabbit AS PW

worked fine for me (Strangely, I didn't use these exact terms :D).

[Rabbit]

Hmm, perhaps not. I don't know where I got the idea from.

[NeoPa]

I would guess from one of those clever self-linking sub-queries you're so good at ;) There it would certainly make sense.