Results 1 to 4 of 4
  1. #1
    excelitforward is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Posts
    2

    Filter irrelevant data from "groups" (Access 2007)

    Hi guys, I am working with MS Access and I am trying to build a query that is able to look at multiple codes, and their results, and then filter the entire group (or not).




    So basically it looks like this. (imagine this is two columns in access)

    Code:
        Code        Result
        253    
        253         5
        253    
        62363    
        362366      4
        7554753     8
        7554753     8
        7554753     8
        7554753    
        7554753     8
        53263    
        3632    
        3632    
        3632    
        3632    
        25235       2
        235636    
        34234




    What I am trying to do, is to filter out both any single "code" that has a blank, or any group of codes that **all** have blanks.


    So for example the end result would be.



    Code:
        Code        Result
        253    
        253         5
        253    
        362366      4
        7554753     8
        7554753     8
        7554753     8
        7554753    
        7554753     8
        25235       2




    Now I have no problem doing this for single lines or what not, but I am really having trouble wrapping my head around doing entire groups. Does anyone have any ideas or suggestions?


    Thanks.


    If I am not too clear, please ask any questions.

    Just to clarify, I want to remove all "groups" of "code" that are blank in result.So if a number in "code" is blank in result, it would filter it out.
    The thing is though, I want this to apply to the entire groups. So if an entire group (say all the 253s) are blank, than it filters them all out, but if even a single one of them has a number. It would keep it.


    Thank you.

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    first make a Select Query with the criteria of the Result field: Not Is Null

    save that with a name NotNulls

    Then make a new Select Query with both your table and NotNulls - - joining them by the Code fields

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,938
    try

    SELECT *
    FROM myTable
    WHERE Exists(SELECT * FROM myTable AS T WHERE Code=myTable.Code and Result is not null)

  4. #4
    excelitforward is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2016
    Posts
    2
    Thank you both NTC and Ajax, both of your methods work to do what I am looking for but in different ways. This has really helped me understand the capabilities of Access. Again, thanks a lot.

    I can say that my original issue has been solved with either of your methods. If someone has any ideas for other ways to do this that would be interesting too.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Filter "Missing Data" Using VBA
    By r0v3rT3N in forum Programming
    Replies: 12
    Last Post: 07-20-2012, 12:40 PM
  2. Replies: 1
    Last Post: 05-23-2012, 10:05 AM
  3. Replies: 2
    Last Post: 03-29-2012, 08:49 AM
  4. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  5. Replies: 13
    Last Post: 07-27-2011, 12:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums