Get max date record from group of records 2

[KeyserSoze]

I have a Microsoft Access query that gives me a list of records from a table. Each record has an identifiable key based on QUARTER_CODE, PERSON_ID_NUMBER and APPLICATION_NUMBER:

SELECT DECS_HIST.PERSON_ID_NUMBER,
       DECS_HIST.QUARTER_CODE,
       DECS_HIST.APPLICATION_NUMBER, 
       DECS_HIST.APPLICATION_DATE
       FROM DECS_HIST
WHERE (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![Current_Term])) OR 
      (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![One_Year]) AND ((DECS_HIST.APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_One_Year])) OR 
      (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![Two_Year]) AND ((DECS_HIST.APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_Two_Year]))
ORDER BY DECS_HIST.QUARTER_CODE, DECS_HIST.PERSON_ID_NUMBER, DECS_HIST.APPLICATION_NUMBER, DECS_HIST.APPLICATION_DATE;

I need to determine the record within each QUARTER_CODE/PERSON_ID_NUMBER/APPLICATION_NUMBER group with greatest APPLICATION DATE.

So far I am stuck at how to formulate toe MAX logic.

Thanks in advance!

 

[PHV]

SELECT DECS_HIST.PERSON_ID_NUMBER,
       DECS_HIST.QUARTER_CODE,
       DECS_HIST.APPLICATION_NUMBER, 
       [!]Max([/!]DECS_HIST.APPLICATION_DATE[!]) AS LastDate[/!]
       FROM DECS_HIST
WHERE (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![Current_Term])) OR 
      (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![One_Year]) AND ((DECS_HIST.APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_One_Year])) OR 
      (((DECS_HIST.QUARTER_CODE)=[Forms]![MAIN]![Two_Year]) AND ((DECS_HIST.APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_Two_Year]))
[!]GROUP BY DECS_HIST.QUARTER_CODE, DECS_HIST.PERSON_ID_NUMBER, DECS_HIST.APPLICATION_NUMBER[/!]
ORDER BY DECS_HIST.QUARTER_CODE, DECS_HIST.PERSON_ID_NUMBER, DECS_HIST.APPLICATION_NUMBER

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KeyserSoze]

Thanks! It works great.

However, when I add another field to just return in the results, I get the error:

You tried to execute a query that does not include the specified expression 'GROUP_NAME' as part of an aggregate function.

Is there a special way that I can add this field just to return with the results?

 

[Golom]

Add the field to the GROUP BY clause as well as the select

OR

use an aggregate function for the new field.

 

[KeyserSoze]

What would be an appropriate aggregate function to use on a character field that takes no part in the decision process of the query? It seems if I add the field to the GROUP BY clause, it would ruin the logic.

 

[PHV]

What about this ?

SELECT D.PERSON_ID_NUMBER,D.QUARTER_CODE,D.APPLICATION_NUMBER,D.APPLICATION_DATE,D.OtherField
FROM DECS_HIST D INNER JOIN (
SELECT PERSON_ID_NUMBER,QUARTER_CODE,APPLICATION_NUMBER,Max(APPLICATION_DATE) AS LastDate FROM DECS_HIST
WHERE (((QUARTER_CODE)=[Forms]![MAIN]![Current_Term])) OR 
      (((QUARTER_CODE)=[Forms]![MAIN]![One_Year]) AND ((APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_One_Year])) OR 
      (((QUARTER_CODE)=[Forms]![MAIN]![Two_Year]) AND ((APPLICATION_DATE)<[Forms]![MAIN]![Cutoff_Week_Two_Year]))
GROUP BY PERSON_ID_NUMBER,QUARTER_CODE,APPLICATION_NUMBER
) M ON D.PERSON_ID_NUMBER=M.PERSON_ID_NUMBER AND D.QUARTER_CODE=M.QUARTER_CODE
   AND D.APPLICATION_NUMBER=M.APPLICATION_NUMBER AND D.APPLICATION_DATE=MLastDate
ORDER BY D.QUARTER_CODE,D.PERSON_ID_NUMBER,D.APPLICATION_NUMBER

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KeyserSoze]

It worked like a thing of beauty. Thanks Golom and PHV.

 

[Andrzejek]

KeyserSoze,
it is customary on this forum to use:

Thank PHV
and star this post!

And/or

Thank Golom
and star this post!

In the lower/left corner of their posts that helped you.

This way others will know the responses were helpful.


Have fun.

---- Andy