Time Attendance query in Microsoft access

Question

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.

Should provide data as text tables, not images. Makes it easier for us to build tables in Access for analysis and testing. Or provide SQL to build and populate tables. Could post in a forum that allows attaching files and provide your db for analysis. — June7, Jul 23 '22 at 04:08
Does this answer your question? [Query for getting value from another record in same table and filter by difference greater than a gap threshold](https://stackoverflow.com/questions/42903785/query-for-getting-value-from-another-record-in-same-table-and-filter-by-differen) — June7, Jul 23 '22 at 05:31
Only 195 (a non-existing user) is checking out and hasn't checked in. So how have you calculated the total hours, please? — Gustav, Jul 23 '22 at 07:39
@Gustav This a demo result as I wanted it to be for an example not a accurate result — abed dak, Jul 23 '22 at 08:59
Please provide a true sample set of data with realistic check-ins and check-outs, as well as the desired result from this. — Gustav, Jul 23 '22 at 09:37
Do the users have to check-out the same day? or can it go over midnight? to other days. Will you always run the query for a single day? — tinazmu, Jul 25 '22 at 00:13

score 0 · Answer 1 · answered Jul 25 '22 at 00:39

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.

score 0 · Answer 2 · answered Jul 25 '22 at 07:28

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 '%2022-07-01%' AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE '%2022-07-01%'

Time Attendance query in Microsoft access

2 Answers2