none
Creating Finished Times RRS feed

  • Question

  • Hello everyone,

    I have a table in a Access 2010 database called tblTaskTimes. Below is some sample data from that table. I want to create a field in a query called "Finished". The Finished time is the next TaskTime record for the Employee. When the Punch Out record is reached for an employee, the Finished time should be the same as the Punch Out record. I'm thinking I may need some type of VBA loop. I'm also including an example of what I need the data to look like.

    Thanks for your help. Kevin

    Here's what I have (tblTaskTimes)

    Here's what I need my query to return:

    Monday, December 21, 2015 1:42 PM

Answers

  • You could use

    SELECT A.*, IIf([A].[TaskID]="PO",[A].[TaskTime],[B].[TaskTime]) AS Finished
    FROM tblTaskTimes AS A LEFT JOIN tblTaskTimes AS B ON A.UniqueID = B.UniqueID-1;


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KevinATF Monday, December 21, 2015 7:25 PM
    Monday, December 21, 2015 4:09 PM

All replies

  • Will the UniqueIDs for a "run" from Punch In to Punch Out always be a contiguous range, as in your example? Or could the UniqueIDs for different "runs" be intermingled?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 21, 2015 2:16 PM
  • Hi Hans,

    Thanks for your fast reply. The UniqueIDs (which is a MS Access autonumber) will be a contiguous range as in my example.

    Thanks for your help.

    Kevin

    Monday, December 21, 2015 3:43 PM
  • You could use

    SELECT A.*, IIf([A].[TaskID]="PO",[A].[TaskTime],[B].[TaskTime]) AS Finished
    FROM tblTaskTimes AS A LEFT JOIN tblTaskTimes AS B ON A.UniqueID = B.UniqueID-1;


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KevinATF Monday, December 21, 2015 7:25 PM
    Monday, December 21, 2015 4:09 PM
  • The UniqueIDs (which is a MS Access autonumber) will be a contiguous range as in my example.

    Thanks for your help.

    I would not be happy with relying on an autonumber.  It only takes one user to abort the insertion of a row to produce a gap in the sequence.  In any case I don't see that sequence helps here as you are not joining one row to the next, but to the next with the same TaskID.

    Instead join one instance of the table to another and return the MIN TaskTime value from the second instance where the TaskID values are equal and the TaskTime value is greater than that of the first instance.


    Ken Sheridan, Stafford, England

    Monday, December 21, 2015 7:06 PM
  • Hi Hans,

    Your suggestion works great. Thank you very much!

    Ken, thank you for your input. The database I'm creating just for writing a report. The data is extracted via pass through from a table in our management system to a temporary table which has a autonumber field type.

    Thank you both.

    Kevin

    Monday, December 21, 2015 7:25 PM