CODEWITHSUNDEEP
databasevbams-access
Deuk Roy
Deuk Roy

Reputation: 15

MS Access Query for time In/Out attendance

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,

USERID  CHECKTIME
5001    12/09/2011  09:05:34
5002    12/09/2011  09:33:13
5001    12/09/2011  13:05:53
5002    12/09/2011  13:22:24
5001    12/09/2011  14:05:22
5002    12/09/2011  14:33:53
5001    12/09/2011  18:05:09
5002    12/09/2011  17:44:34

This is the image enter image description here

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')

This is the image enter image description here

Please help me out to make this query,

Upvotes: 0

Views: 460

Answers (1)

forpas
forpas

Reputation: 164099

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

Upvotes: 1

Related Questions