Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You set the table and queries for Right-to-Left orientation. Doesn't make any difference to the calcs, just threw me off a bit.



    Problem seems to be the queries are pairing dates from different days. Restrict the IN/OUT pairing to same day. If IN/OUT periods can cross midnight (shift assignments cross midnight), this will get very complicated and probably need VBA and is beyond my available time and interest to figure out.

    SELECT CHECKINOUT.USERID, Format(IIf([CHECKTYPE]="I",[CHECKTIME],Null),"mm/dd/yyyy hh:nn:ss") AS CHECKIN, Format((SELECT TOP 1 Dupe.CHECKTIME FROM CHECKINOUT AS Dupe WHERE Dupe.UserID=CHECKINOUT.UserID
    AND Dupe.CHECKTYPE="O" AND Dupe.CHECKTIME>CHECKINOUT.CHECKTIME AND Day(Dupe.CHECKTIME)=Day(CHECKINOUT.CHECKTIME)),"mm/dd/yyyy hh:nn:ss") AS CHECKOUT, CHECKINOUT.SENSORID AS DEVICEIN, (SELECT TOP 1 Dupe.SENSORID FROM CHECKINOUT AS Dupe WHERE Dupe.UserID=CHECKINOUT.UserID AND Dupe.CHECKTYPE="O" AND Dupe.CHECKTIME>CHECKINOUT.CHECKTIME AND Day(Dupe.CHECKTIME)=Day(CHECKINOUT.CHECKTIME)) AS DEVICEOUT
    FROM CHECKINOUT
    WHERE (((CHECKINOUT.CHECKTYPE)="I"));

    SELECT CHECKINOUT.USERID, Format((SELECT TOP 1 Dupe.CHECKTIME FROM CHECKINOUT AS Dupe WHERE Dupe.UserID=CHECKINOUT.UserID
    AND Dupe.CHECKTYPE="I" AND Dupe.CHECKTIME<CHECKINOUT.CHECKTIME AND Day(Dupe.CHECKTIME)=Day(CHECKINOUT.CHECKTIME)),"mm/dd/yyyy hh:nn:ss") AS CHECKIN, Format(IIf([CHECKTYPE]="O",[CHECKTIME],Null),"mm/dd/yyyy hh:nn:ss") AS CHECKOUT, (SELECT TOP 1 Dupe.SENSORID FROM CHECKINOUT AS Dupe WHERE Dupe.UserID=CHECKINOUT.UserID
    AND Dupe.CHECKTYPE="I" AND Dupe.CHECKTIME<CHECKINOUT.CHECKTIME AND Day(Dupe.CHECKTIME)=Day(CHECKINOUT.CHECKTIME)) AS DEVICEIN, CHECKINOUT.SENSORID AS DEVICEOUT
    FROM CHECKINOUT
    WHERE (((CHECKINOUT.CHECKTYPE)="O"));
    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.

  2. #17
    yakirdvi is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    10
    Its Right-to-Left automatic because my operating system in Hebrew...

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time In Time Out Attendance
    By gatsby in forum Access
    Replies: 7
    Last Post: 04-10-2014, 09:30 AM
  2. Replies: 7
    Last Post: 03-28-2013, 06:41 AM
  3. Replies: 42
    Last Post: 03-01-2013, 06:58 AM
  4. Replies: 5
    Last Post: 12-12-2011, 08:08 AM
  5. Replies: 3
    Last Post: 12-23-2010, 10:23 AM

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