Getting the max value and the month and hour it occurred in access

Question

**I have the following table:

ClientID  ClientName  Power   Date        Month Hour
123456    aaa         558     11/2/2014   11    5
123456    aaa         1558    11/2/2014   11    6
123456    aaa         1238    11/2/2014   11    7
123456    aaa         458.48  11/2/2014   11    8
789000    bbb         800.48  11/2/2014   11    5
789000    bbb         190.10  11/2/2014   11    6
789000    bbb         909     11/2/2014   11    7
789000    bbb         405     11/2/2014   11    8
777888    ccc         702     11/2/2014   11    1

I tried to get the clientid, clientname, max(power), month and hour for a specific month and year, I have tried several queries, the closer I got was using:

SELECT clientid, clientname, max(max_power), date, hour
FROM (SELECT clientid, clientname, max(Power) AS max_power, date, hour FROM 
tabledata WHERE month(date) = 11 and year(date) = 2014 
GROUP BY clientid, clientname, date, hour)  AS t
GROUP BY clientid, clientname, date, hour;

But I get all the distinct hours, I just want the date and hour of the max value for each client.

Can anyone help me.

Answer 1

Something like this:

select A.clientid, A.clientname, A.power, A.date, A.hour
from tabledata A
JOIN
(
    SELECT clientid, max([power]) as max_power
    from tabledata
    group by ClientID
) as B on A.ClientID = b.ClientID and A.[power] = B.max_power