Results 1 to 4 of 4
  1. #1
    vguzman77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    1

    Query group by date, get punch in and punch out time in separate field

    Hi eveybody, I need to figure out a query on a database I am working.


    Basically the records are coming from a MS SQL server and I am pulling something similar to the info below:

    userid date date/time
    10325 10/14/2018 10/14/2018 7:01:08
    12532 10/14/2018 10/14/2018 7:05:03
    10584 10/14/2018 10/14/2018 8:30:01
    10325 10/14/2018 10/14/2018 13:31:02
    10325 10/14/2018 10/14/2018 14:30:05
    10325 10/14/2018 10/14/2018 17:08:02
    12532 10/14/2018 10/14/2018 13:01:34
    10584 10/14/2018 10/14/2018 14:08:03
    10325 10/14/2018 10/15/2018 7:35:03
    10325 10/15/2018 10/15/2018 13:14:12

    These are records when the employees punched in and punched out from work. I need to get a query that shows the following:

    userid date punchin punchout
    10325 10/14/2018 7:01:08 13:31:02
    10325 10/14/2018 14:30:05 17:08:02
    12532 10/14/2018 7:05:03 13:01:34
    10584 10/14/2018 8:30:01 14:08:03
    10325 10/15/2018 7:35:03 13:14:12

    Is this even possible ? I need to group them by userid and date, then to make sure the punchin is earlier than the punchout, is only one pair of punches is available, do not show it. No changes on the MS SQL server can be done before bringing the data. Eveything has to be done in Access.

    Any help is appreciated.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without a definite indication of punchin/out the assumption has to be the earlier time is the punch in and the later time is the punch out. What will be difficult is if there are not two events per user/day. But assuming that is not an issue you need to join the table to itself - something like (note the use of Aliases)


    Code:
    SELECT A.userid, A.date, A.datetime as punchin, B.datetime as punchout
    FROM tblTimes A INNER JOIN tblTimes B ON A.userid=B.userID and A.date=B.Date
    WHERE A.datetime<B.datetime
    ORDER BY A.userid, A.date
    note date is a reserved word and should not be used as a field name

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The output example doesn't quite make sense. The last pair for 10325 shows checkin on 10/14/2018 and checkout on 10/15/2018. Cannot drop the date part if you want to correctly calculate elapsed time.

    The datein and dateout should not be on same record of raw data. Workable structure would be:
    ID userid DateTime InOrOut
    1 10325 10/14/2018 7:01:08 AM IN
    2 10325 10/14/2018 1:31:02 PM OUT

    Even having the InOrOut field in your original structure would be workable.

    And now I see Ajax answer. Hope the query works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just assumed it was a typo and if not should not have been in the example result.

    However if not a typo and should be in the results, the alternative perhaps would be to use the date part of the datetime field instead of the date field

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-16-2018, 04:58 PM
  2. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  3. Replies: 1
    Last Post: 03-13-2014, 07:23 PM
  4. managings punch lists
    By masoud_sedighy in forum Sample Databases
    Replies: 4
    Last Post: 07-07-2013, 10:37 AM
  5. Add sequence number to time punch table
    By aflamin24 in forum Queries
    Replies: 1
    Last Post: 07-20-2012, 05:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums