sql query in ms access

See more:

SQL-Server

MS-Access

Im using Ms access database and my table structure looks like,

<br />
EmpID   InDate             InTime         Type<br />
---------------------------------------------<br />
25      03-Sep-12   9:35:35 AM  IN<br />
25  03-Sep-12   10:31:32 AM OUT<br />
25  03-Sep-12   10:34:13 AM IN<br />
25  03-Sep-12   11:05:08 AM OUT<br />
25  03-Sep-12   11:08:39 AM IN<br />
25  03-Sep-12   12:13:28 PM OUT<br />
25  03-Sep-12   12:18:56 PM IN<br />
25  03-Sep-12   1:44:50 PM  OUT<br />
25  03-Sep-12   1:51:06 PM  IN<br />
25  03-Sep-12   2:01:15 PM  OUT<br />
25  03-Sep-12   2:21:15 PM  IN<br />
25  03-Sep-12   2:22:10 PM  OUT<br />

Another table called emp which contains the list of employees.

The expected output is,

<br />
EmpID  Name   Duration   outduration<br />
----------------------------------------<br />
   25   xyz    4:08:39     0:17:56<br />

I use below query,

SELECT
    Sub.EmpID,sub.firstname + sub.lastname as Name,
     (24*Int(Sum(sub.end_time - sub.start_time))+Format(Sum(sub.end_time - sub.start_time),'h')) & Format(Sum(sub.end_time - sub.start_time),':nn:ss') AS duration,
     (24*Int(Sum(sub1.outend_time - sub1.outstart_time))+Format(Sum(sub1.outend_time - sub1.outstart_time),'h')) & Format(Sum(sub1.outend_time - sub1.outstart_time),':nn:ss') AS outduration
FROM
    (
        SELECT
            o.SNo,
            o.InTime AS end_time,
            o.EmpID,
            o.Type,
            o.InDate,
            (
                SELECT TOP 1 i.InTime
                FROM EmpLog AS i
                WHERE
                        i.Type = 'IN'
                    AND i.EmpID = o.EmpID
                    AND i.InTime < o.InTime
                   and i.InDate=o.InDate
                ORDER BY i.InDate, i.InTime DESC
            ) AS start_time,e.firstname,e.lastname
        FROM EmpLog AS o,emp as e WHERE o.Type='OUT'  and e.emp_ID=o.EmpID and o.InDate>=#9/3/2012# and o.InDate<=#9/3/2012#
    ) AS sub,

(select
      ot.InTime as outend_time,
       (
         SELECT TOP 1 it.InTime
        FROM EmpLog AS it
              WHERE it.Type = 'OUT' AND it.EmpID = ot.EmpID AND it.InTime < ot.InTime and it.InDate=ot.InDate ORDER BY it.InDate, it.InTime DESC
            ) AS outstart_time
          From EmpLog as  ot, emp as ep where   ot.Type='IN'  and ep.emp_ID=ot.EmpID and ot.InDate>=#9/3/2012# and ot.InDate<=#9/3/2012#   and
          Format(ot.InTime - (SELECT TOP 1 it.InTime
                FROM EmpLog AS it
                WHERE
                        it.Type = 'OUT'
                    AND it.EmpID = ot.EmpID
                    AND it.InTime < ot.InTime and it.InDate=ot.InDate
                ORDER BY it.InDate, it.InTime DESC), 'hh:nn:ss')<="00:10:00")  as sub1

 group by sub.EmpID,sub.lastname,sub.firstname order by sub.firstname asc;

But the output of the query,

<br />
EmpID  Name   Duration   outduration<br />
----------------------------------------<br />
   25   xyz    20:43:15     0:17:56  

If i execute sub and sub2 as seperate queries. It gives correct answer. But if i group them into 1 the inner query that is sub.duration is wrong. Can any 1 suggest me where is the problem.