Question about Max() Function And Group By

[stal]

(Access 2007) I have a table with 2 columns: 'Sample_Time' and 'num_of_messages'.
the table contain around 400 records for each day. I want to make a query which will give me as output the time (date and time) and 'num_of_messages' where maximum 'num_of _messages' appear for every day (group by date).

I tried to run the following query but it gives me an error: "You tried to execute a query that does not include the specified expression 'Format([Tbl_message].[Sample_Time],"Short Time")' as part of an aggregate function":

SELECT Max(Tbl_message.[num_of_messages]) AS [max messages], (Format([Tbl_message].[Sample_Time],"Short Time")) AS 'Sample Time'
FROM Tbl_message
GROUP BY (Format([Tbl_message].[Smaple_Time],"Short Date"));

I can only run the following but it gives me only the date without the time which is not good enough for me:

SELECT Max(Tbl_message.[num_of_messages]) AS [max messages], (Format([Tbl_message].[Sample_Time],"Short Time")) AS 'Sample Time'
FROM Tbl_message
GROUP BY (Format([Tbl_message].[Smaple_Time],""Short Time"));


does this mean that I cant use different Time format? how to overcome this?

Thanks for Your help

 

[plog]

In an aggregate query (one that uses the GROUP BY clause), you need to include in the GROUP BY clause every field that you are not performing a calculation on (i.e. using MAX(), MIN(), COUNT(), etc.) that is in the SELECT clause.

The initial SQL you posted fails because you are using one field in the SELECT clause ((Format([Tbl_message].[Sample_Time],"Short Time")) AS 'Sample Time') and another in the GROUP BY clause ((Format([Tbl_message].[Smaple_Time],"Short Date")). These two must be the same for your query to work.

From what I understand you should probably use this query:

SELECT Max(Tbl_message.[num_of_messages]) AS [max messages], [Sample_Time]
FROM Tbl_message
GROUP BY [Smaple_Time];

If not, look through the Format function documentation to find a format that will produce what you want. Just be sure to use it in both the SELECT and GROUP BY clauses.