Results 1 to 3 of 3
  1. #1
    jim5six is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    2

    I cannot figure out how to get the query results I need

    I am having a problem getting the results I need. I have a dataset that lists files sent, type of communication, status...among other data. I need to get results that show only the records that status = "FAILED", however only if there is no success via type of communication.
    Example date:
    ID FileName CommType Status
    1 file1 email success
    2 file1 fax fail
    3 file1 email success


    4 file2 fax fail
    5 file3 email fail
    6 file4 fax success
    7 file4 email success
    8 file2 email fail

    Results needed:
    Rows 4;5;8 only

    Thank you very much for any light you can shed on this.
    Jim

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    get all the success
    Q1 = select * from table where [status] = 'SUCCESS'

    now for this next query , make an OUTER join to find the ones NOT in Q1
    join on FILENAME, COMTYPE
    show all records in Q2, and some recs in Q1
    filter Q2.filename = null to find the records without a match in Q1

    like:
    Q2 = select * from table, Q1 left join on Q2.filename =Q1.filename , Q2.comtype =Q1.comtype [status] = 'FAIL' and filename is null

  3. #3
    jim5six is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    2
    RanMan thank you,

    That was exactly what I needed.

    -Jim

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

Similar Threads

  1. Can't figure out how to do this query
    By delorean004 in forum Queries
    Replies: 1
    Last Post: 12-22-2014, 04:33 AM
  2. Replies: 3
    Last Post: 05-09-2012, 05:27 PM
  3. Can't figure out Access query
    By luderbeck1 in forum Queries
    Replies: 3
    Last Post: 02-02-2012, 12:12 PM
  4. Need a query to figure all possible combinations
    By julestrip in forum Queries
    Replies: 1
    Last Post: 05-27-2011, 07:23 AM
  5. Cannot Figure this query out
    By ryan1313 in forum Queries
    Replies: 6
    Last Post: 08-13-2010, 12:54 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