none
Query criteria IN RRS feed

  • Question

  • I'm blanking out in this query 101 issue.

    I have a table tAgents with two fields, SalesPerson and State.  I'm trying to build a query that will list all SalesPerson that sell ONLY in NJ and FL.  So the result would return Maria and Bill but not Tom because he also sells in PA.

    No VBA please just SQL, please help.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Sunday, March 11, 2018 2:29 PM

Answers

  • Perhaps something along the line of

    SELECT tAgents.SalesPerson, tAgents.State
    FROM tAgents
    WHERE (((tAgents.SalesPerson) Not In (SELECT tAgents.SalesPerson
    FROM tAgents
    WHERE (((tAgents.State) Not In ("NJ","FL"))))));
    


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by DriveEV Sunday, March 11, 2018 4:45 PM
    Sunday, March 11, 2018 3:42 PM

All replies

  • Perhaps something along the line of

    SELECT tAgents.SalesPerson, tAgents.State
    FROM tAgents
    WHERE (((tAgents.SalesPerson) Not In (SELECT tAgents.SalesPerson
    FROM tAgents
    WHERE (((tAgents.State) Not In ("NJ","FL"))))));
    


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by DriveEV Sunday, March 11, 2018 4:45 PM
    Sunday, March 11, 2018 3:42 PM
  • Wow! The infamous double Not In.

    Thanks a million.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Sunday, March 11, 2018 4:46 PM