How to use MAX or DMAX to get the results by day not by column

[Auditboy]

Good Afternoon, I am try to get Max or Dmax for each date not the whole column. If I use the MAX for Message Date/Time it will take the MAX date for the whole column. I want to get the Max date for each day. Any ideas or thought? using Access 2010. Thank you


Message Text Message Date/Time
iSTAR Input 'Kerr Hall Door 001 Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 200C Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 200D Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 200F Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:02 AM
iSTAR Input 'Keer Hall Door 100 Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:03 AM
iSTAR Input 'Keer Hall Door 200 Local Alarm Bypass [KERR HALL]' is active. 8/27/2018 7:50:03 AM
iSTAR Input 'Keer Hall Door 100 Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM
iSTAR Input 'Keer Hall Door 200 Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 001 Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 200C Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM
iSTAR Input 'Kerr Hall Door 200D Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM


iSTAR Input 'Kerr Hall Door 200F Local Alarm Bypass [KERR HALL]' is active. 8/28/2018 7:50:02 AM

[pbaldy]

Something like

SELECT DateValue(DateTimeField), Max(DateTimeField) AS MaxValue
FROM TableName
GROUP BY DateValue(DateTimeField)

[Auditboy]

Not very familiar with SQL. How would you write the expression in access?

[pbaldy]

That is Access SQL. You can start a new query, switch to SQL view and copy that in, changing the names as appropriate.

[Auditboy]

I will try that. Thank you

[pbaldy]

No problem, post back if you get stuck. Welcome to the site by the way!

[Auditboy]

I keep getting this error.

This is what I currently have

SELECT [1 LA_Active_TBtest].[Message Text], [1 LA_Active_TBtest].[Message Date/Time]
FROM [1 LA_Active_TBtest]
GROUP BY [1 LA_Active_TBtest].[Message Text], [1 LA_Active_TBtest].[Message Date/Time], [1 LA_Active_TBtest].Inactive
ORDER BY [1 LA_Active_TBtest].[Message Date/Time];



The Message Date/Time has the time in it. Not sure if this makes a difference or not. Example. 8/27/18 7:50:45 AM

Thank you for your time.

[pbaldy]

Well, you didn't use the DateValue() function, nor the Max(), and you added a new field. If you need the message text too, try this two-query method:

http://www.baldyweb.com/LastValue.htm