How to Query attendance on MS-ACCESS

Question

I want to query an attendance from ms-access with one table on it and calculate the number of hrs work :

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

Now, I want to display them like this :

emp_id  emp_name  emp_date           time-in  time-out  HrsWork
1       mike      20140819-20140820  210010   051005    9
2       tess      20140819-20140819  075910   171011    10 

Any help would be appreciated. Thanks

Answer 1

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.