Max Demand  Date    Hour    Store Number
420 7/1/2019    19  516
415 7/1/2019    20  6228
390 7/1/2019    17  520
402 7/1/2019    20  1363
357 7/1/2019    22  8949

SELECT [Time and Demand].Demand, [Time and Demand].[Store Number], Dates.Hour, Dates.ThisDate
FROM Dates INNER JOIN [Time and Demand] ON Dates.[Date and Time] = [Time and Demand].[Date and Time]

WHERE Demand IN

(SELECT Max([Time and Demand].Demand) AS MaxOfDemand, [Time and Demand].[Store Number], Dates.ThisDate

FROM Dates INNER JOIN [Time and Demand] ON Dates.[Date and Time] = [Time and Demand].[Date and Time])


GROUP BY [Time and Demand].[Store Number], Dates.ThisDate;