Query that excludes rows with criteria in multiple columns

[JAM7007]

I am new to Access and have been hitting a wall and seem to be finding nowhere that has this same type of task.

I have a table(Table 1 ) with 3 columns...

Table 1






I am trying to run a query that returns Table 1 but excludes rows that meet multiple criteria. I need to return rows that do NOT contain the combination of criteria below

Criteria

1) Lot Number - Left([Lot Number],2) = "PT"
2) Transaction Desc. - "Put-away"


Basically, i want exclude a "Lot number" starting with "PT" IN COMBINATION WITH a "Transaction Desc" of "Put-away". The goal is for the query to return ROW2 and ROW3 and exclude ROW1.

ROW1 would be excluded from the query result because it meets both criteria.
ROW2 and ROW3 would be included because it does not fully meet all criteria.


Any help on this would be of great help!

[Dal Jeanis]

Mathematically, these all achieve the same result.
The first one subselects all the records to kick out, then kicks them out of the select.
The second version accepts each record unless it matches both criteria together.
The third version accepts each records that fails to match either criteria individually.

Take your Pick:

SELECT (fields)
FROM MyTable
WHERE MyKey NOT IN 
   (SELECT T2.MyKey
    FROM MyTable AS T2
    WHERE (( Left(T2.[Lot Number],2)  = "PT")
    AND (T2.[Transaction Desc] = "Put-Away")));

or

SELECT (fields)
FROM MyTable
WHERE NOT 
   ( 
   (Left([Lot Number],2)  = "PT") AND
   ([Transaction Desc] = "Put-Away")
   );

or

SELECT (fields)
FROM MyTable
WHERE 
   (
   NOT( Left([Lot Number],2) = "PT")
OR NOT([Transaction Desc] = "Put-Away")
   );

[JAM7007]

Thank you very much, Dal. This have been a great solution and learning process for me and was able to save vast amounts of time. Code 2 seemed to be very effective.

[Dal Jeanis]

Happy to oblige. Please mark the thread solved. Top of the page, under "Thread tools".