Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    Unanswered: Max Checkin max Checkout for the same day

    Hi,
    I have a table with CheckInOut and its structure : UserID, CheckTime(Date/time),CheckType, where CheckType is CheckIn Or a CheckOut




    Code:
    SELECT 
    UserID, 
    CheckTime AS CheckInTime, 
    DMin("CheckTime", "tblCheckInOut", "UserID=" & UserID & " AND CheckTime > #" & Format(CheckTime, "yyyy-mm-dd hh:nn:ss") & "# AND tblCheckInOut.CheckType='O'") AS CheckOutTime
    FROM tblCheckInOut WHERE CheckType='I'
    the statement works great but if the user checked in twic IN THE SAME DATE 11/18/2012 8:30:00 AM & 11/18/2012 8:31:00 AM and check out just one time 11/18/2012 8:31:01 AM I get the below result


    Code:
    USERID    TimeIn                       TimeOut               D
    3    11/18/2012 8:30:00 AM    11/18/2012 8:31:01 AM    11/18/2012
    3    11/18/2012 8:31:00 AM    11/18/2012 8:31:01 AM    11/18/2012
    3    11/19/2012 8:30:00 AM                              11/19/2012
    3    11/20/2012 8:30:00 AM                            11/20/2012
    the problem is I need to view the first checkin time and last checkout for the same date if I have more than check in & out

    thank you very much

    MEM

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Do it in 2 seperate queries - 1 for max in time and 1 for max out time - and then link them into your main query.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Nov 2012
    Posts
    4
    I tried creating the two queries using Totals query instead of using domain aggregate function before posting this question, and Joined the two queries using UserID, but the results have been not accurate at all.

  4. #4
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Did you add a date field to the 2 queries?
    You will need to add date fields and link on those as well as the UserID and you need to allow for blanks to so don't use hard joins.
    Regards
    JD

    Software-Matters

  5. #5
    Join Date
    Nov 2012
    Posts
    4
    I attached a sample that describes the table structure
    Please, take a look
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Hi, Please see the attached file and let me know if this is what you need.
    Attached Files Attached Files
    Regards
    JD

    Software-Matters

  7. #7
    Join Date
    Nov 2012
    Posts
    4
    Hi, JD

    Thank you very much

    That's exactly what I need to do

    I really appreciate your GREAT help

    thank you a gain

    dbprojecttoday

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •