Access SQL NOT IN multiple criteria

[MCTampa]

I have a small query which works in the following setup.

SELECT
O.[Check In Year],
O.Cost,
O.UCDX,
O.[Area#],
O.RESX,
O.[Check-In],
O.Units,
O.[All Inclusive],
O.CMGX

From Output O left Join DVW D On
O.RESX = D.RESX

Where O.RESX NOT IN
(
Select RESX
From DVW
)

This identifies RESX which are not on the table DVW.

But I need the criteria to be more detailed

On my left join, I need to add:
O.UCDX = D.Size
AND O.[Check-In] = DVW.EXDTFDTF

But I can't seem to get it to work properly in the NOT IN portion of the query.

I was trying to build it out like this:

SELECT
O.[Check In Year],
O.Cost,
O.UCDX,
O.[Area#],
O.RESX,
O.[Check-In],
O.Units,
O.[All Inclusive],
O.CMGX

From Output O left Join DVW D On
O.RESX = D.RESX
AND O.UCDX = D.SIZE

Where O.RESX NOT IN
(
Select RESX
From DVW
)
AND
Where O.UCDX NOT IN
(
Select UCDX
From DVW
);

 

[welshgasman]

I do not think you can have the second where?

 

[MCTampa]

I do not think you can have the second where?

I agree, but I can't figure out how to write it correctly.

 

[welshgasman]

Just remove the second where.

 

[MCTampa]

But in this context the other criteria need to be met as well.

 

[welshgasman]

Just try it.

 

[Micron]

Make a union query out of the 2 Selects that make up the two Not In and use that query name in to Not Select from?

 

[FormR]

Bit late to the party! But isn't this what you want:

SELECT
    O.[Check In Year],
    O.Cost,
    O.UCDX,
    O.[Area#],
    O.RESX,
    O.[Check-In],
    O.Units,
    O.[All Inclusive],
    O.CMGX
From 
    Output O 
        left Join DVW D 
            On O.RESX = D.RESX
            and O.UCDX = D.Size
            AND O.[Check-In] = D.EXDTFDTF
Where 
    D.RESX is null