MS Access SQL Query for time In/Out attendance

Question

HI,

I'm doing a project which involves time and attendance management. When I download data from the biometric reader, I got the records in the following format,

Lunch Break & Work Time Problem

Attendance Log Table Name: dbo_CHECKINOUT The user Table name is: dbo_USERINFO

+---------+-----------------------+
| USERID  |       CHECKTIME       |
+---------+-----------------------+
|       5 | 2/16/2020 9:33:08 AM  |
|       2 | 2/16/2020 9:57:48 AM  |
|       3 | 2/16/2020 10:07:31 AM |
|       4 | 2/16/2020 10:36:01 AM |
|       1 | 2/16/2020 11:10:10 AM |
|       3 | 2/16/2020 1:45:15 PM  |
|       5 | 2/16/2020 1:57:46 PM  |
|       2 | 2/16/2020 1:58:19 PM  |
|       3 | 2/16/2020 2:17:46 PM  |
|       2 | 2/16/2020 2:33:39 PM  |
|       5 | 2/16/2020 2:48:26 PM  |
|       1 | 2/16/2020 7:51:57 PM  |
|       3 | 2/16/2020 9:29:20 PM  |
|       5 | 2/16/2020 9:29:25 PM  |
|       2 | 2/16/2020 9:29:29 PM  |
|       4 | 2/16/2020 9:29:46 PM  |
|       5 | 2/17/2020 9:31:47 AM  |
|       3 | 2/17/2020 10:15:13 AM |
|       4 | 2/17/2020 10:28:54 AM |
|       1 | 2/17/2020 11:28:17 AM |
+---------+-----------------------+

I want to show the above records as follows, (the Log_In, LB_Out, LB_In, Log_Out, WorkTime and LunchBreak are based on 'time') Someone from StackOverFlow helped me out to make this query

SELECT t.userid, dbo_USERINFO.NAME, DateValue(t.checktime) AS [date], 
Max(IIf(t.counter=0,t.checktime,Null)) AS Log_In, 
Max(IIf(t.counter=1,t.checktime)) AS LB_Out, 
Max(IIf(t.counter=2,t.checktime,Null)) AS LB_In, 
Max(IIf(t.counter=3,t.checktime)) AS Log_Out, 
Format((Log_In-LB_Out)+(LB_In-Log_Out),"hh:nn:ss") AS WorkTime, 
Format(LB_In-LB_Out,"hh:nn:ss") AS LunchBreak
FROM (
    SELECT t.*, 
      (select count(*) from dbo_CHECKINOUT where userid = t.userid and datevalue(checktime) = datevalue(t.checktime) and checktime < t.checktime) AS [counter] FROM dbo_CHECKINOUT AS t)  AS t INNER JOIN dbo_USERINFO ON t.USERID=dbo_USERINFO.USERID
GROUP BY t.userid, dbo_USERINFO.NAME, DateValue(t.checktime);

Results:

userid      date         Log_In        LB_Out        LB_In       Log_Out    WorkTime    LunchBreak
1           16-Feb-20   11:10:10 AM   7:51:57 PM                
1           17-Feb-20   11:28:17 AM                 
2           16-Feb-20   9:57:48 AM    1:58:19 PM    2:33:39 PM  9:29:29 PM  10:56:21     00:35:20
3           16-Feb-20   10:07:31 AM   1:45:15 PM    2:17:46 PM  9:29:20 PM  10:49:18     00:32:31
3           17-Feb-20   10:15:13 AM                 
4           16-Feb-20   10:36:01 AM   9:29:46 PM                
4           17-Feb-20   10:28:54 AM                 
5           16-Feb-20   9:33:08 AM    1:57:46 PM    2:48:26 PM  9:29:25 PM  11:05:37     00:50:40
5           17-Feb-20   9:31:47 AM

Now the problem is, as you can see Userid:1 & Userid: 4 doesn't have a LunchBreak. So their 1st log would be Log_In & 2nd Log would be as a Log_Out & total work time would be between 1st(Log_In) & 2nd(Log_Out).

Please help to make this possible for me.

Query from previous question here: [MS Access Query for time In/Out attendance](https://stackoverflow.com/q/60237040/1014587) — Mast, Feb 17 '20 at 08:40

Gustav · Accepted Answer · 2020-02-18T08:26:03.097

0

This fancy query will return the lunch breaks:

SELECT 
    dbo_UserInfo.UserId, 
    DateValue([CheckTime]) AS [Date], 
    TimeValue(Min([CheckTime])) AS LogIn, 

    (Select Max(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime) 
    And T.CheckTime >
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))) As LogOut,

    (Select Min(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime)
    And T.CheckTime > 
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    And T.CheckTime <
        (Select Max(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    Having Count(*) > 1) As LBIn,

    (Select Max(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime) 
    And T.CheckTime > 
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    And T.CheckTime <
        (Select Max(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    Having Count(*) > 1) As LBOut
FROM 
    dbo_UserInfo
GROUP BY 
    dbo_UserInfo.UserId, 
    DateValue([CheckTime]);

From this you can easily calculate the work hours.

Revised output:

edited Feb 18 '20 at 08:26

answered Feb 17 '20 at 09:06

Gustav

53,498
7
29
55

Thanks for the query. I copy your query & try to run it, it asks me some parameter value. What should i do? – Deuk Roy Feb 17 '20 at 09:47
My User Table & Log Table are deferent, dbo_CHECKINOUT for Log & dbo_USERINFO for employee – Deuk Roy Feb 17 '20 at 09:50
Adjust the table and field names to match yours and it will run. – Gustav Feb 17 '20 at 09:53
Hi, @Gustav, It's Working now. But, I have still some issues. a person how didn't log out yet, His/Her log out field remain login time – Deuk Roy Feb 17 '20 at 11:25
Is there any other way to leave the field blank until logout? – Deuk Roy Feb 17 '20 at 11:27
Hi, I have tried to run this query, **Duplicate output alias 'LogOut'** – Deuk Roy Feb 18 '20 at 06:22
Removed the duplicate. – Gustav Feb 18 '20 at 06:24
I did Remove the duplicate and run the query. But still having some issues. So I posted an `Answer` below. – Deuk Roy Feb 18 '20 at 07:15
Then double-check. It will run, as you can see. – Gustav Feb 18 '20 at 07:16
It's running OK. But if he/she forgot to logout their last log becomes LogOut & Lunch Break remains the same. – Deuk Roy Feb 18 '20 at 07:22
You can filter out those values with `Having Count(*) > 1`. See edited answer, please. – Gustav Feb 18 '20 at 08:27
Thanks! @Gustav, All OK now. – Deuk Roy Feb 18 '20 at 12:07

MS Access SQL Query for time In/Out attendance

Lunch Break & Work Time Problem

1 Answers1