you tried to execute a query that does not include the specified expression 'out_time' as an aggregate function in ms access

Question

Im using Ms access as my database and I'm using following query for getting the time worked:

select 
        in_time,
        out_time,
        datediff("n",b.in_time,c.out_time) as work_time,
        log_date,
        emp_id 
from 
    (select 
        LogTime as in_time,
        SrNo,
        LogID as emp_id,
        LogDate as log_date 
    from LogTemp 
    where Type='IN' ) as b
left join
    (select 
        SrNo as out_id, 
        LogTime as out_time,
        LogID as out_emp_id,
        LogDate as out_log_date 
      from LogTemp 
     where Type = 'OUT'
     group by SrNo) as c
on (b.SrNo <> c.out_id
    and b.emp_id = c.out_emp_id
    and b.log_date = out_log_date ) 
where  
    c.out_id > b.SrNo and 
    [log_date] >= #8/20/2012# and 
    [log_date] <= #8/20/2012# and 
    emp_id = "8" 
group by b.SrNo; 

But when I execute the query Im getting the following error:

"you tried to execute a query that does not include the specified expression 'out_time'
 as an aggregate function in ms access" error.

Any suggestion where I'm making the mistake.

Answer 1

You have a couple of mistakes, in case you are trying to do a GROUP BY. First of all check the MSDN for GROUP BY syntax, recommendations and samples.

Basically, without going deeper, if you use GROUP BY, any column on the SELECT clause not affected by an aggregate function as SUM, AVG, etc, should appear on the GROUP BY clause. So in your case you should add:

LogTime as out_time,
LogID as out_emp_id,
LogDate as out_log_date

Into the GROUP BY of the 2nd subquery. And add

 in_time,
 out_time,
 datediff("n",b.in_time,c.out_time) as work_time,
 log_date,
 emp_id 

On the main GROUP BY at the end.

But, as already pointed out on one comment, maybe what you want to do is an ORDER BY. Then should be as easy as replacing GROUP by ORDER and it should work. Just be sure is that what you want.

Answer 2

The derived table C at LEFT JOIN does not need any ordering or grouping. There is no reason I can see why it should not match the derived table B at FROM.

left join
    (select 
        SrNo as out_id, 
        LogTime as out_time,
        LogID as out_emp_id,
        LogDate as out_log_date 
      from LogTemp 
     where Type = 'OUT') as c

The final statement of the outer query should be ORDER BY (as mentioned) because the outer query does not have any aggregate functions.

I suspect you will have problems with an explicit join on a no match with MS Access, so you might like to consider moving it to a WHERE statement.

 on (b.SrNo <> c.out_id