Need help cross-time records table for time attendance

[June7]

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"));

[yakirdvi]

Its Right-to-Left automatic because my operating system in Hebrew...