Maximum by date

[yoonie_85]

Hey,

i'm not sure if i'm posting this in the right forum, since i'm not sure if I should be trying to solve my problem using a query or a module.

I was wondering if anybody knows how to get around this...I have to find the maximum value of each month (May - September) for each person (there are 30 different people). the format of my table looks like this:

ID MonthByNumber HighestValue
ABC 5/1/2000 0.1
ABC 5/2/2000 0.4
ABC 5/3/2000 5.0
ABC 5/4/2000 6.2

Thanks so much for your help.

 

[Cynoclast]

SELECT ID, Format(MonthByNumber,"m") AS Month, Max (HighestValue) AS Maximum
FROM myTable
GROUP BY ID, Format(MonthByNumber,"m");

I think this only works in access (not portable to other DBMSes) due to the Format() function. This will also run slower than a query without the Format() function.