Find Max Date from a Distinct Row Then Add Value to a Table Column

Question

Quick disclaimer - I am familiar with Access, but a novice at MySql within Access.

I have a table with activity data that I pulled into a query to filter down to only necessary data. The query table has DateTime, Call, and Designator columns. The Call and Designators rows repeat numerous times. The Call is a numeric value which identifies a unique activity and there may be many Designators assigned to each Call. Within this query, I need to find the Max and Min DateTime values of distinct rows (based on the Call and Designator rows) and then add a "Start" value to a new column for the Min DateTime and "End" for the Max DateTime.

Once the Start and End times have been identified for each unique row (Call and Designator) I need to calculate the total time each Designator spent on each call. I assume that I may have to create another query to include DateTime, Designator, Call, Start Time, End Time, Total Time on a single row to generate reports. But, it would be much easier if it can all be done with one script on the same query.

I've been searching for the past several days and haven't made any progress so I don't have any existing work to show.

Thank you in advance!!

Answer 1

Test

SELECT MIN([Date/Time]) [Date/Time],
       Call,
       Designator,
       MIN([Date/Time]) AS [Start Time],
       MAX([Date/Time]) AS [End Time],
       DateDiff("h",[Start Time],[End Time]) & ":" & 
       DateDiff("n",[Start Time],[End Time]) Mod 60 & ":" & 
       DateDiff("s",[Start Time],[End Time]) Mod 60 AS [Total Time]
FROM [table with activity data]
GROUP BY Call,
         Designator;

If you need in leading zeros in [Total Time] then add according FORMAT().