Time Attendances Query in Microsoft Access

MS Access Query for time In/Out attendance

You can group by userid and date and then use conditional aggregation:

select t.userid, datevalue(t.checktime) as [date],
  max(iif(t.counter = 0, t.checktime, null)) as Log_In,
  max(iif(t.counter = 1, t.checktime, null)) as LB_Out,
  max(iif(t.counter = 2, t.checktime, null)) as LB_In,
  max(iif(t.counter = 3, t.checktime, null)) as Log_Out,
  Format((Log_In - LB_Out) + (LB_In - Log_Out), "HH:mm:ss") as WorkTime,
  Format(LB_In - LB_Out, "HH:mm:ss") as LunchBreak
from (
  select t.*,
    (select count(*) from tablename where userid = t.userid and datevalue(checktime) = datevalue(t.checktime) and checktime < t.checktime) as counter
  from tablename as t
) as t
group by t.userid, datevalue(t.checktime)

Results:

userid  date        Log_In                  LB_Out                  LB_In                   Log_Out                 WorkTime  LunchBreak
5001    12/9/2011   12/9/2011 9:05:34 am    12/9/2011 1:05:53 pm    12/9/2011 2:05:22 pm    12/9/2011 6:05:09 pm    08:00:06  00:59:29
5002    12/9/2011   12/9/2011 9:33:13 am    12/9/2011 1:22:24 pm    12/9/2011 2:33:53 pm    12/9/2011 5:44:34 pm    06:59:52  01:11:29

MS Access SQL Query for time In/Out attendance

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.

Sample Image

Revised output:

Sample Image

How to Query attendance on MS-ACCESS

Keep in mind that using ctrl+k will allow you to type using code lines, which will help you keep things lined up.

I say this because it took me about an hour to fully understand what you posted at the top. The line below:

emp_id emp_name emp_date emp_time emp_dept emp_mode
1 mike 20140819 201040 security 5 1 mike 20140820 051005 security 4 2 tess 20140819 074910 hr 5 2 tess 20140819 171011 hr 4

should translate to:

emp_id emp_name emp_date    emp_time      emp_dept        emp_mode
1       mike    2014/08/19  20:10:40      security         5 
1       mike    2014/08/20  05:10:05      security         4 
2       tess    2014/08/19  07:49:10      hr               5 
2       tess    2014/08/19  17:10:11      hr               4

Which then gave me a better understanding of what you were trying to accomplish.

but we all live and learn. I think you are looking for something like the below SQL statement.

SELECT HourCalc.emp_id, HourCalc.emp_name, HourCalc.StartDateTime, HourCalc.EndDateTime, DateDiff('n',[startdatetime],[enddatetime])/60 AS HrsWorked
FROM (SELECT t1.emp_id, t1.emp_name, CDate([emp_date] & ' ' & [emp_time]) AS StartDateTime, 
           (SELECT MIN(cdate(t2.emp_date & ' ' & t2.emp_time))
            FROM emptable t2
            WHERE t2.Emp_id = t1.Emp_ID
            AND cdate(t2.emp_date & ' ' & t2.emp_time) > cdate(t1.emp_date & ' ' & t1.emp_time)
            AND emp_mode = '4') AS EndDateTime
      FROM emptable AS t1
      WHERE (((emp_mode) = '5'))) as HourCalc;

On each where clause, if your emp_mode is a number field data type, just remove the single quotes around the number 4 and 5.

This might not be the exact layout you posted in your question, but it should put you on the right track.

If this doesn't work, let me know and we can do more digging to find you the correct SQL statement you are looking for.

Time Attendances query in Microsoft Access

Let's start with the answer from the previous question, and work our way from there.

This query defines if it's a check in, or check-out. Let's call it qryCheckInOut

SELECT EmployeeID, 
    timeInOut, 
    IIF(
        (SELECT COUNT(*) 
        FROM MyTable s 
        WHERE s.EmployeeID = m.EmployeeID 
        AND s.timeInOut <= m.timeInOut
        AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1, "I", "O") As OriginType
FROM MyTable m

Then, we can get the check-ins from that query, and use a subquery to get the check-outs.

We use conditions to make sure the check out is on the same day, and later than the check in, and use the Min aggregate to make sure it's the next time (the lowest possible time).

SELECT q.EmployeeID, 
    q.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM qryCheckInOut s
    WHERE s.EmployeeID = q.EmployeeId
    AND s.TimeInOut > q.TimeInOut
    AND s.TimeInOut <= Int(q.TimeInOut) + 1) As TimeOut
FROM qryCheckInOut q
WHERE q.OriginType = 'I'

Note that, in the subquery of the second query, you don't need to check if it's a check in or check out, since the lowest time higher than the check in on the same day always is a check out.

If you want to do it in a single query, you can use the query below. However, it will be substantially harder to debug

SELECT m.EmployeeID, 
    m.TimeInOut As TimeIn,
    (SELECT Min(s.TimeInOut)
    FROM MyTable s
    WHERE s.EmployeeID = m.EmployeeId
    AND s.TimeInOut > m.TimeInOut
    AND s.TimeInOut <= Int(m.TimeInOut) + 1) As TimeOut
FROM MyTable m
WHERE 
    (SELECT COUNT(*) 
    FROM MyTable s 
    WHERE s.EmployeeID = m.EmployeeID 
    AND s.timeInOut <= m.timeInOut
    AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1

Attendances query in Microsoft Access

You can use a correlated subquery and the modulus operator for this:

SELECT EmployeeID, 
    timeInOut, 
    IIF(
        (SELECT COUNT(*) 
        FROM MyTable s 
        WHERE s.EmployeeID = m.EmployeeID 
        AND s.timeInOut <= m.timeInOut
        AND s.timeInOut >= INT(m.timeInOut)) Mod 2 = 1, "I", "O") As OriginType
FROM MyTable m

This query works in the following way:

The subquery gets the amount of rows for that employee that have been posted on the same date as the current row. Then, we calculate the modulus of 2 of that count, returning 1 if the count is not divisible by 2 (e.g. the 1st, 3rd, 5th etc check-in), and 0 if it's not.

If the count is divisible by 2, then it must be a check in, if it's not, it's a check out.

MS Access Query to get entry time and exit time for each ID

This is an aggregation query:

select AttendanceDate, min(AttendanceTime) as entrytime,
       max(AttendanceTime) as exittime, CardId
from t
group by AttendanceDate, CardId;

To add this to another table, you would use either select into (to create the table) or insert (if the table is already created).

The insert would look like this:

insert into t2(AttendanceDate, entrytime, exittime, CardId)
    select AttendanceDate, min(AttendanceTime) as entrytime,
           max(AttendanceTime) as exittime, CardId
    from t
    group by AttendanceDate, CardId;

I don't believe MS Access supports "upsert", which would be a single operation to do both. So, create a unique index on the two columns:

create unique index unq_t_AttendanceDate_CardId on t(AttendanceDate, CardId);

Then attempt to do an insert. If that fails, then do an update. Here is a question on that topic.

Calculate timely attendance per month

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.