1

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.

Expected Result

Thank you in advance!!

2
  • 1
    Provide example data as table-formatted text which can be copypasted to the table.
    – Akina
    Aug 5 at 4:57
  • Does output Date/Time and Start Time have the same value always?
    – Akina
    Aug 5 at 4:58
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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.