CODEWITHSUNDEEP
sqlms-accessfilter
Manny
Manny

Reputation: 103

Excluding Records based on Multiple Filter Criteria- SQL Access

I have a predefined set of filter criteria that should exclude records if the expression proves true. Ex: Exclude records where Trade Status = 'MAT' AND exclude records where Portfolio is IN ('Assume', 'HDLTV'). An AND function doesnt work because it would look for records where both conditions are true. An OR function wont work either because if a record is held true by one and not the other, then it will return the record.

Is there some way to perhaps exclude records where the filter criteria is met for multiple conditions. The goal is to filter the unwanted records out of the original dataset. In the query below im just trying to get the excluded population first, just to try things out. Any help is much appreciated.

Trade ID    Trade Status    Trade Type  Portfolio
             VER            BondForward HD7
             VER           BondForward  HD7
             VER               EQOPT    HD7
             VER               EQOPT    HD7
 82689       VER              FUTURE    INCOMFLEX
 82104       VER              FUTURE    CAPHEDGE

SELECT HedgeFile.[Trade ID], HedgeFile.[Trade Status], HedgeFile.[Trade Type], HedgeFile.Portfolio FROM HedgeFile WHERE (HedgeFile.[Trade Status] = 'MAT') OR HedgeFile.[Portfolio] IN ('ASSUME', 'HDLTV', 'INCOMFLEX', 'CAPHEDGE') OR (HedgeFile.Hedgecode = 'WSP') OR (HedgeFile.[Trade Type] <> 'FUTURE') order by HedgeFile.[Trade Type]

Upvotes: 0

Views: 984

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want not. One way to write this is:

SELECT HedgeFile.[Trade ID], HedgeFile.[Trade Status], HedgeFile.[Trade Type], 
HedgeFile.Portfolio
FROM HedgeFile
WHERE (NOT HedgeFile.[Trade Status] = 'MAT') OR
      (NOT HedgeFile.[Portfolio] IN ('ASSUME', 'HDLTV', 'INCOMFLEX', 'CAPHEDGE') OR
      (NOT HedgeFile.Hedgecode = 'WSP') OR
      (NOT HedgeFile.[Trade Type] <> 'FUTURE')
order by  HedgeFile.[Trade Type]

Of course, you can simplify this to:

SELECT HedgeFile.[Trade ID], HedgeFile.[Trade Status], HedgeFile.[Trade Type], 
HedgeFile.Portfolio
FROM HedgeFile
WHERE (HedgeFile.[Trade Status] <> 'MAT') OR
      (HedgeFile.[Portfolio] NOT IN ('ASSUME', 'HDLTV', 'INCOMFLEX', 'CAPHEDGE') OR
      (HedgeFile.Hedgecode <> 'WSP') OR
      (HedgeFile.[Trade Type] = 'FUTURE')
order by  HedgeFile.[Trade Type];

The logic may be slightly more complicated if any of the columns can take on NULL values.

Upvotes: 1

Related Questions