Calculate timely attendance per month

Question

I want to calculate amount of came on time or early of employee per specific date. But in the example i put specific USERID as additional criteria.

This is my CHECKINOUT table (Total 39 rows):

USERID      CHECKTIME       CHECKTYPE   VERIFYCODE  SENSORID    WorkCode        sn          UserExtFmt      Update          
1040    02/03/2020 6:54:50      I           1           3           0       0840060140610       0       02/03/2020 10:13:56 
1040    02/03/2020 8:00:00      I           1           2           0       0840060140160       0       02/03/2020 10:50:20 
1040    02/03/2020 16:34:37     I           1           5           0       2809731360643       0       26/03/2020 9:51:41  
1040    03/03/2020 8:02:41      I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    03/03/2020 16:45:00     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    03/03/2020 16:45:03     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    04/03/2020 7:57:46      I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    04/03/2020 7:57:48      I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    04/03/2020 17:01:53     I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    04/03/2020 17:01:56     I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    05/03/2020 8:03:45      I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    05/03/2020 8:03:48      I           1           2           0       0840060140160       0       26/03/2020 9:50:49  
1040    05/03/2020 16:41:02     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    05/03/2020 16:41:05     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    06/03/2020 8:27:13      I           1           2           0       0840060140160       0       26/03/2020 9:50:50  
1040    06/03/2020 17:26:03     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    06/03/2020 17:26:06     I           1           5           0       2809731360643       0       26/03/2020 9:51:42  
1040    07/03/2020 11:53:57     I           1           2           0       0840060140160       0       26/03/2020 9:50:50  
1040    09/03/2020 8:01:51      I           1           2           0       0840060140160       0       27/03/2020 10:29:16 
1040    16/03/2020 7:58:20      I           1           2           0       0840060140160       0       26/03/2020 9:50:52  
1040    16/03/2020 7:58:22      I           1           2           0       0840060140160       0       26/03/2020 9:50:52  
1040    16/03/2020 16:34:07     I           1           5           0       2809731360643       0       26/03/2020 9:51:43  
1040    17/03/2020 7:59:05      I           1           2           0       0840060140160       0       26/03/2020 9:50:52  
1040    17/03/2020 16:43:50     0           1           5           0       2809731360643       0       26/03/2020 9:51:44  
1040    18/03/2020 8:00:43      I           1           5           0       2809731360643       0       26/03/2020 9:51:44  
1040    18/03/2020 8:00:46      I           1           5           0       2809731360643       0       26/03/2020 9:51:44  
1040    18/03/2020 16:30:23     I           1           2           0       0840060140160       0       26/03/2020 9:50:52  
1040    19/03/2020 8:03:24      I           1           2           0       0840060140160       0       26/03/2020 9:50:53  
1040    19/03/2020 17:13:44     I           1           2           0       0840060140160       0       26/03/2020 9:50:54  
1040    20/03/2020 8:10:41      I           1           3           0       0840060140610       0       26/03/2020 9:51:10  
1040    20/03/2020 8:10:44      I           1           3           0       0840060140610       0       26/03/2020 9:51:10  
1040    20/03/2020 17:01:41     I           1           5           0       2809731360643       0       26/03/2020 9:51:44  
1040    23/03/2020 8:00:07      I           1           2           0       0840060140160       0       26/03/2020 9:50:54  
1040    23/03/2020 16:38:09     I           1           5           0       2809731360643       0       26/03/2020 9:51:45  
1040    24/03/2020 7:59:08      I           1           5           0       2809731360643       0       26/03/2020 9:51:45  
1040    24/03/2020 7:59:11      I           1           5           0       2809731360643       0       26/03/2020 9:51:45  
1040    24/03/2020 16:39:30     I           1           2           0       0840060140160       0       26/03/2020 9:50:55  
1040    24/03/2020 16:39:33     I           1           2           0       0840060140160       0       26/03/2020 9:50:55  
1040    26/03/2020 8:10:31      I           1           3           0       0840060140610       0       26/03/2020 9:51:11

This is my CHECKEXACT looks like:

EXACTID     USERID      CHECKTIME   
404         1040    09/03/2020 8:01:51

I've tried to achieve this using SUM aggregate function and IIf condition, but unfortunately the query give me wrong result.

This is my query:

SELECT af.USERID, SUM(
        IIf(af.CHECKTIME Is Not Null,
            IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:15:00', 
                1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
            ),
            IIf(bf.CHECKTIME Is Not Null,
                IIf(WeekDay(DateValue(bf.CHECKTIME)) <> 6 And Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:15:00', 
                    1, IIf(Format(bf.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
                ), 0
            )
        )
    ) AS [Came On Time or Early]  
FROM (CHECKINOUT AS af
    LEFT JOIN CHECKEXACT bf ON  af.USERID = bf.USERID)
    WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#  GROUP BY af.USERID

Above query returns this result:

USERID  Came On Time or Early
1040            441

As we know if we COUNT CHECKINOUT table it would return 39 rows And CHECKEXACT only return 1 row. But the query returns 441 as [Came On Time or Early].

I don't know what is wrong with my query, i think i put the right query to get the total of came on time or early of employee with USERID = 1040 at March 2020.

Could you tell me what is wrong with my query ?

I have tried to get the correct result for about a week but still hasn't solved yet. — Dont Bullying Me, Apr 30 '20 at 10:00
Your data shows international date structure. This may contribute to issue. Review http://allenbrowne.com/ser-36.html — June7, Apr 30 '20 at 15:45
@June7 : I think the problem came from query which is nested `IIf()` function. Because the given date criteria give me the correct result. By the way the access database is generated by attendance management system, here the link for the software : [link](https://interactive.co.id/cart/downloads) — Dont Bullying Me, May 02 '20 at 15:47
I built tables and ran your query. It returns 21. I had to change date values in table to U.S. structure. — June7, May 02 '20 at 18:08

Dont Bullying Me · Accepted Answer · 2020-05-05T16:30:42.257

Thanks to @June7 to response this question through the comment.

After checked multiple times i realized that i wrote wrong query especially at this line :

IIf(WeekDay(DateValue(af.CHECKTIME)) <> 6 And Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 
                1, IIf(Format(af.CHECKTIME, 'hh:nn:ss') <= '08:30:00', 1, 0)
            )

I should separate ...WeekDay(DateValue(af.CHECKTIME)) <> 6... to another part. My previous query would take Friday to be not Friday if af.CHECKTIME time is greater than '08:30:00' while the day is Friday. And then would jump to IIf on the false part where i suppose to operate another weekday.

And also i should change this line :

...
FROM (CHECKINOUT AS af
    LEFT JOIN CHECKEXACT bf ON  af.USERID = bf.USERID)
...

To this :

...
FROM(
    SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
        FROM CHECKINOUT AS af
    WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
    GROUP BY af.USERID, DateValue(af.CHECKTIME) 
        UNION
    SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
        FROM CHECKEXACT AS bf
    WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
    GROUP BY bf.USERID, DateValue(bf.CHECKTIME) 
)
...

Because in the previous query, From would print all of datetime between the given range and LEFT JOIN CHECKEXACT would print data of CHECKEXACT that only a row (09/03/2020 8:01:51 every checking would be true because smaller than '08:30:00' and off course would print 1) repeatedly as many as CHECKINOUT's rows while i only need to check presence comes which is only the minimum datetime of each days both of CHECKINOUT and CHECKEXACT.

So the right complete query would look like this:

SELECT USERID, 
    SUM(IIf(WeekDay(DateValue([Tanggal dan Waktu])) <> 6,
            IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'), 
                1, 0
            ),                                      
            IIf(TimeValue([Tanggal dan Waktu]) <= TimeValue('08:30:00'), 1, 0)
        )
    )
    AS [Came On Time or Early]
FROM(
    SELECT af.USERID, MIN(af.CHECKTIME) AS [Tanggal dan Waktu]
        FROM CHECKINOUT AS af
    WHERE af.USERID = 1040 And af.CHECKTIME Between #3/1/2020# And #3/31/2020#
    GROUP BY af.USERID, DateValue(af.CHECKTIME) 
        UNION
    SELECT bf.USERID, MIN(bf.CHECKTIME) AS [Tanggal dan Waktu]
        FROM CHECKEXACT AS bf
    WHERE bf.USERID = 1040 And bf.CHECKTIME Between #3/1/2020# And #3/31/2020#
    GROUP BY bf.USERID, DateValue(bf.CHECKTIME) 
)
GROUP BY USERID

The above query would print 6 as the correct / desired [Came On Time or Early] record.

Calculate timely attendance per month

1 Answers1