-1

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

Chris
  • 254
  • 1
  • 5
dlanorfeb24
  • 3
  • 3

1 Answers1

0

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.

Chris
  • 254
  • 1
  • 5
  • You're welcome. Please mark this post as answered if this is the solution to your problem. – Chris Aug 28 '18 at 00:35
  • hi Chris, i've tried to execute the query on my access it seems there has to be a problem with HourCalc – dlanorfeb24 Aug 28 '18 at 01:27
  • Hi Chris, i try to modify something on your example on my C# but it gives me an error "syntax error on FROM clause" below is my code. – dlanorfeb24 Aug 28 '18 at 13:37
  • SelectHourCalc.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 t1 WHERE – dlanorfeb24 Aug 28 '18 at 13:53
  • (((emp_mode)=5))) AS HourCalc" + " AND t1.emp_date between #" + dtFromDate.Value.Date.ToString("yyyyMMdd") + "# AND #" + dtToDate.Value.Date.ToString("yyyyMMdd")+"#"; – dlanorfeb24 Aug 28 '18 at 13:53
  • the query that i've execute on my ACCESS 2013 displays only the emp_id,emp_name but the startdatetime,enddatetime and hrsworked marked as #ERROR ...kindly check as well on my C# code on top i've tried to use your sample query on my program.. Thanks – dlanorfeb24 Aug 28 '18 at 18:22
  • please check below query that i modified and executed on MS-ACCESS 2013 but it gives me this "#NUM!" on columns [startdatetime],[enddatetime],[HrsWorked] – dlanorfeb24 Aug 28 '18 at 21:01
  • SELECT HourCalc.emp_id, HourCalc.emp_name, HourCalc.StartDateTime, HourCalc.EndDateTime, DateDiff ('n',cdate ([startdatetime]) – dlanorfeb24 Aug 28 '18 at 21:05
  • ,cdate([enddatetime]) ) /60 AS HrsWorked FROM ( SELECT t1.emp_id, t1.emp_name, CDate (format( [t1.emp_date],"yyyymmdd") + timevalue ( format( [t1.emp_time] ,"hh:mm:ss")) ) AS StartDateTime, ( SELECT MIN (cdate (format( t2.emp_date,"yyyymmdd") + timevalue (formatt2.emp_time,"hhmmss"))) ) FROM EmpTable t2 WHERE t2.emp_id = t1.emp_id AND cdate ( format(t2.emp_date,"yyyymmdd") + timevalue (format(t2.emp_time,"hhmmss")) ) > cdate ( format(t1.emp_date,"yyyymmdd") + timevalue (format(t1.emp_time ,"hhmmss")) ) AND l_mode = 4) AS – dlanorfeb24 Aug 28 '18 at 21:06
  • EndDateTime FROM EmpTable AS t1 WHERE t1.emp_date='20180820' AND ( ((l_mode) = 5))) as HourCalc ; – dlanorfeb24 Aug 28 '18 at 21:06
  • Sorry for the late response, no intention on leaving you hanging. But it might be a better option to ask a new question pertaining to C#. Your original question asked for a query to produce a desired result. My C# skills are beginner level so I will be unable to answer your question. but by asking a new question and starting a new thread, you can find someone who will be able to assist you better. – Chris Aug 29 '18 at 13:10
  • me too my C# level of skills is for beginner ...anyway thanks a lot for your time and effort – dlanorfeb24 Aug 29 '18 at 14:40