"NOT IN" does not work

[lienok]

Please help me.
I have a select with "Not In" but it does not work. No idea what is wrong. See below:

SELECT var_rates.lnr
FROM var_rates
WHERE (((var_rates.start_period) Is Not Null) AND ((var_rates.end_period) Is Null))
GROUP BY var_rates.lnr;

lnr
486
487
530
531
540

Then I have
SELECT var_rates.lnr FROM var_rates
GROUP BY var_rates.lnr
HAVING (((Max(var_rates.end_period))>Now()-7 And (Max(var_rates.end_period))<Now()+7))

lnr
820
824
825

Notice please 820,824,825 are bit in previous result!
After I connect those two select to one with using "Not In"
SELECT var_rates.lnr FROM var_rates
WHERE
(
(
(var_rates.lnr) Not In
(
SELECT var_rates.lnr FROM var_rates WHERE
(
((var_rates.start_period) Is Not Null) And
((var_rates.end_period) Is Null)
)
GROUP BY var_rates.lnr;
)
)
) GROUP BY var_rates.lnr
HAVING (((Max(var_rates.end_period))>Now()-7 And (Max(var_rates.end_period))<Now()+7))

so I should have as a result
820,824,825 because they are in var_rates with mentioned condition and in the same time they are NOT IN mentioned Select result.

Hope you understand my point and you can give me any advice. thank you

 

[lienok]

I forget to add..
that from the last query I have NO RESULT
(No idea why)

 

[Golom]

Look at your HAVING clause. The first MAX should be "start_period" ... not "end_period".

 

[lienok]

Actually I am interested just in "end_period".
No need of "start_period"

 

[madonnac]

you would have to do a "LEFT JOIN"

example:

SELECT NovellIDs.DATA_VALUE
FROM NovellIDs LEFT JOIN TCS_LOGIN ON NovellIDs.DATA_VALUE = TCS_LOGIN.USR_NAME
WHERE (((TCSDBOWNER_TCS_LOGIN.USR_NAME) Is Null));

This selects everything from NovellIDs where there is no corresponding value in TCS_LOGIN