Time Attendance query in Microsoft access-CodePudding

I am having troubles with writing a query in Microsoft Access. This is how my tables looks like and where I want to retrieve data from:

I want a query that has the following result:

As you can see in the first table a user according to CHECKTYPE can check IN 'I' and check OUT 'O' so I was trying to use subqueries and Datediff() to get the difference but I am kind new to subqueries concept and by that I can't get the result I want.

CodePudding user response：

You can use a self-join (to get the Checkout time for any Checkin row); with a subquery (to remove multiple checkout rows, if the same user checks in/out multiple times).

This one assumes that you only have one day's worth of data in the table, and it prints multiple rows for each session (of checkin/checkout):

SELECT 
    CI.USERID, 
    format(dateadd('n',
            datediff('n', CI.CHECKTIME, CO.CHECKTIME),
             #01/01/1900#),'hh:nn') as HHMM
FROM MyTbl CI
    inner join 
    MyTbl CO
    ON  CO.USERID=CI.USERID
WHERE 
           CO.CHECKTIME >= CI.CHECKTIME
   AND CO.CHECKTYPE = 'O'
   AND CI.CHECKTYPE = 'I'
   AND NOT EXISTS 
       (SELECT *
        FROM MyTbl COO
        WHERE COO.USERID=CO.USERID
             AND COO.CHECKTIME>=CI.CHECKTIME
             AND COO.CHECKTYPE='O'
             AND COO.CHECKTIME < CO.CHECKTIME
        );

The addition to 1900-01-01 is done to turn the minutes found into a datetime value, so that I could use the format function to show in the format you want.

CodePudding user response：

Thanks all I got my answer:

 Select  (B.Name),FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL  from(
[CHECKINOUT] AS A
INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID  )
INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID where A.CHECKTYPE='I' and C.CHECKTYPE='O' AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%' AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%'