Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    SubQuery "NOT IN"

    I am trying to write a SubQuery to find the records that are not in a range. It is bringing me the records that match, not the rest. Seems like the NOT function isn't working.
    Code:
    SELECT YSOC_AvaiL1.BasicMaterial, YSOC_AvaiL1.QtyKCS, YSOC_AvaiL1.Material, 
     YSOC_AvaiL1.ProdOrigin, YSOC_AvaiL1.MG, Left([ProdOrigin],2) AS Country, Right([ProdOrigin],2) AS Region
    FROM YSOC_AvaiL1
    WHERE (((Left([ProdOrigin],2)) 
     Not In (SELECT [YEXCLUSION]![Grower Country] FROM yexclusion 
     WHERE  [YEXCLUSION]![Material Group] = [YSOC_AvaiL1]![MG] ;)))
    ORDER BY YSOC_AvaiL1.BasicMaterial, Left([ProdOrigin],2);
    Can somebody tell me what I am doing wrong?
    Alan

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Did you run the subquery on its own, to see if it is really returning the records you want to exclude?

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    I am such a dummy. Went back and looked again; since it returned so few records, I thought it was wrong. Doing exactly what it should do.
    Alan

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    All is well when it was well to begin with .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •