Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015
    Posts
    7

    Unanswered: Returning Attributes

    Hello -

    Having an issue with a MsAccess query.
    My issue is that I have data that has a unique identifier; (CCP_INQ_KEY) within this identifier there are tasks we call "Action" each action has a date & time assigned to it (TaskActionCreatedDate). I am trying to return from this TaskActionCreatedDate the "Action" that matches the Max "TaskActionCreatedDate" date and time.
    Example below; So I know that 05/07/2015 is my Max date/time but when I run the query I only want to bring back the Action that matches that Max TaskActionCreatedDate Not all of the Actions.
    When I use group as my total for Action, I get all Actions; when I use Last or Max as my total for Action I get the last Action Alphbetically listed.

    I feel like I need to utilize the Max function within the formula on the Action column but cannot seem to accomplish anything with that; Something like IIf(Max([TaskActionCreatedDate],[Action],""))
    Any assistance is appreciated. Thanks!

    Example:
    CCP_INQ_KEEY Action TaskActionCreatedDate
    494599092014 CASE DUE DATE 3/31/2015 11:24
    494599092014 CASE RESEARCH 5/5/2015 7:53
    494599092014 CASE DECISION 5/5/2015 7:55
    494599092014 AUTHORIZATION INFO 5/5/2015 7:56
    494599092014 AUTHORIZATION 5/5/2015 9:48
    494599092014 CLAIMS SENT 5/5/2015 9:49
    494599092014 CLAIMS PROCESSED 5/7/2015 9:38

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you want the most recent row for a specific CCP_INQ_KEEY?
    Code:
    SELECT CCP_INQ_KEEY, Action, TaskAction, Max(CreatedDate)
    FROM mytable
    WHERE CCP_INQ_KEEY = 494599092014
    natch you'd need to change mytable for whatever your table is called, and check the column names are correcxt. you may also need to refine the query as its untested

    or if you want the most recent creation date for every CCP_INQ_KEEY
    Code:
    SELECT CCP_INQ_KEEY, Action, TaskAction, Max(CreatedDate)
    FROM mytable
    GROUP BY CCP_INQ_KEEY
    I'm not too sure what you expected to do with
    IIf(Max([TaskActionCreatedDate],[Action],""))
    though
    I cannot comprehend how you woudl use the Max function inside an IIF, a DMAX maybe but that would be tortuous on a big dataset
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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