Thread: Returning Attributes
06-09-15, 13:22 #1Registered User
- Join Date
- May 2015
Unanswered: Returning Attributes
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!
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
06-09-15, 16:24 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
so you want the most recent row for a specific CCP_INQ_KEEY?
SELECT CCP_INQ_KEEY, Action, TaskAction, Max(CreatedDate) FROM mytable WHERE CCP_INQ_KEEY = 494599092014
or if you want the most recent creation date for every CCP_INQ_KEEY
SELECT CCP_INQ_KEEY, Action, TaskAction, Max(CreatedDate) FROM mytable GROUP BY CCP_INQ_KEEYIIf(Max([TaskActionCreatedDate],[Action],""))
I cannot comprehend how you woudl use the Max function inside an IIF, a DMAX maybe but that would be tortuous on a big datasetI'd rather be riding on the Tiger 800 or the Norton