item Max SQL help

J

John

hi id like to get this query -

SELECT HistoryLog.ItemDATE, TimeValue([ItemTIME])
AS ItemTIMEValue, HistoryLog.GENSETNAME, HistoryLog.kWhrs,
HistoryLog.Runhrs, HistoryLog.Pwr, HistoryLog.numst, HistoryLog.REASON
FROM HistoryLog
WHERE (((HistoryLog.REASON)="Time stamp"))
ORDER BY HistoryLog.ItemDATE, TimeValue([ItemTIME]);

to only return the max of ItemTIME. Ive been trying all sorts but cant get
it to work
Many Thanks
 
D

Douglas J. Steele

Are you saying that you want the values of ItemDATE, GENSETNAME, kWhrs,
Runhrs, Pwr, numst and REASON associated with the max of ItemTIME?

SELECT ItemDATE, TimeValue([ItemTIME]) AS ItemTIMEValue,
GENSETNAME, kWhrs, Runhrs, Pwr, numst, REASON
FROM HistoryLog
WHERE REASON="Time stamp"
AND ItemTIME IN (SELECT Max(ItemTime) FROM HistoryLog)
ORDER BY HistoryLog.ItemDATE, TimeValue([ItemTIME]);
 
Top