none
Time difference between records RRS feed

  • Question

  • Hello everyone,

    I'm currently working on a Access 2010 (on Win7) database where I need to calculate the time difference between separate date/time fields between separate records for multiple employees. Here's and example of my table:


    I need to measure in a query the time gap between each EmployeeID's EndDate to the Next StartDate. The results should look like this:


    Most of the time difference between records examples I have found for this type of issue does not include the added caveat such as the EmployeeID factor. Any suggestions is appreciated.

    Thank you, Kevin


    • Edited by KevinATF Thursday, November 5, 2015 3:47 PM
    Thursday, November 5, 2015 3:36 PM

Answers

  • Hi Kevin,

    Untested but you might try something along these lines to see if the times line up correctly:

    SELECT T1.EmployeeID,
     (SELECT Max(T2.EndDate) As LastEndDate
      FROM TableName T2
      WHERE T2.EmployeeID=T1.EmployeeID
        AND T2.EndDate<=T1.StartDate),
     T1.StartDate
    FROM TableName T1

    Hope that helps...
    • Marked as answer by KevinATF Friday, November 6, 2015 4:12 PM
    Thursday, November 5, 2015 3:58 PM

All replies

  • Hi Kevin,

    Untested but you might try something along these lines to see if the times line up correctly:

    SELECT T1.EmployeeID,
     (SELECT Max(T2.EndDate) As LastEndDate
      FROM TableName T2
      WHERE T2.EmployeeID=T1.EmployeeID
        AND T2.EndDate<=T1.StartDate),
     T1.StartDate
    FROM TableName T1

    Hope that helps...
    • Marked as answer by KevinATF Friday, November 6, 2015 4:12 PM
    Thursday, November 5, 2015 3:58 PM
  • Hi DB Guy,

    Thanks for he fast response. I used your statement. The only modification I made to your statement was to replace "TableName" with the name of the table which is "Report Data". When I run it I get a "Enter Parameter Value" dialog box wanting the T1.StartDate value.

    Where did I make my mistake?

    SELECT T1.EmployeeID, (SELECT Max(T2.EndDate) As LastEndDate FROM [Report Data] T2 WHERE T2.EmployeeID=T1.EmployeeID AND T2.EndDate<=T1.StartDate), T1.StartDate FROM [Report Data] T1

    Thursday, November 5, 2015 4:24 PM
  • Double-check the spelling of the field for start date in your table's design view.
    Thursday, November 5, 2015 4:36 PM
  • You are correct. I don't know how to spell (LOL). I corrected that field name. But the times are not lining up correctly. Shouldn't the AutoNumberID be used in the query somewhere?

    Thanks.

    Thursday, November 5, 2015 9:03 PM
  • Hi. I don't think the ID field would be necessary. I couldn't test it because I didn't have any sample data. Can you send me some?
    • Edited by .theDBguy Friday, November 6, 2015 4:46 AM typo
    Thursday, November 5, 2015 10:34 PM
  • Kevin -

    you will make your work easier if you name your tables without using blanks in the name.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, November 6, 2015 1:59 AM
  • Peter-

    I never post the actual names of my fields, tables, queries, etc. These are just examples to post my question. My actual table names do not have spaces in the names.


    • Edited by KevinATF Friday, November 6, 2015 3:25 PM
    Friday, November 6, 2015 3:25 PM
  • Hey DB Guy,

    I found the issue. It was with the raw data itself and not the query.

    Thank you very much for all your help. You the man!

    Friday, November 6, 2015 4:12 PM
  • Hey DB Guy,

    I found the issue. It was with the raw data itself and not the query.

    Thank you very much for all your help. You the man!

    Hi Kevin,

    You're welcome. Glad to hear you were able to get it to work. Good luck with your project.

    Friday, November 6, 2015 9:02 PM