Geek asks and answers

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

I use access to Ms as my database, and I use the following query to get the hours 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 in which I make a mistake.

+5
source share
2 answers

You have a couple of mistakes if you are trying to make one GROUP BY. First check the MSDN for syntax, recommendations, and GROUP BY patterns .

, , GROUP BY, GROUP BY SELECT, SUM, AVG ..). :

LogTime as out_time,
LogID as out_emp_id,
LogDate as out_log_date

GROUP BY .

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

GROUP BY .

, , , , ORDER BY. , GROUP ORDER, . , .

+5

C LEFT JOIN . , , B 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

ORDER BY ( ), - .

, MS Access, WHERE.

 on (b.SrNo <> c.out_id
+1

More articles:


All Articles