Sum between date

  • Thread starter Thread starter pierre
  • Start date Start date
P

pierre

Hi all,

I have 3 fields, employeNb, date and hours
and i want to fing the overtimes hours using somthing like

SELECT INV.empno, Sum(INV.hr) AS SommeDehr
FROM INV
GROUP BY INV.empno
HAVING (((INV.DATE_INV) Between #1/1/2005# And #1/25/2005#));

But, I have an error message regarding agregate function.

In the employe field i put group
In the hour fiel i put sum
In the date field i put expression

What is wrong there?
I want one record for each employe with his total ttime for the date range.

I would like to do that in only one query since i use it as the
recordsource of a form

The user select a date range on a calendar and the form should
display hte total time for each employe.
I dont know how to do that with a query on top of another one.

regards,
Pierre
 
pierre said:
I have 3 fields, employeNb, date and hours
and i want to fing the overtimes hours using somthing like

SELECT INV.empno, Sum(INV.hr) AS SommeDehr
FROM INV
GROUP BY INV.empno
HAVING (((INV.DATE_INV) Between #1/1/2005# And #1/25/2005#));

But, I have an error message regarding agregate function.

In the employe field i put group
In the hour fiel i put sum
In the date field i put expression

What is wrong there?
I want one record for each employe with his total ttime for the date range.
Click to expand...


Change the Having to WHERE

Or in the query design view change the date field from
Expression to Where
 
Change Expression to Where.

This will change the HAVING clause in your SQL to a WHERE clause. They work
in a similar fashion except WHERE operates *before* Grouping (thereby
reducing the number of records being aggregated) while HAVING operates on
the data *after* grouping. For this reason, fields in a HAVING clause must
be in either the SELECT or GROUP BY clauses (HAVING can only "see" the
grouped data). Since DATE_INV is not in the grouped data, an error was
generated.

In any case, WHERE is the more effecient of the two as long as it provides
the needed results, which it should in this case.

Here's an example of when a HAVING clause would be needed. If you wanted to
only show Employees whose Sum(INV.hr) > 40, that would require a HAVING
clause since the data would need to be aggregated before such an evaluation
could be made. In your case, you could either add such a HAVING clause to
your SQL manually or put ">40" on the Criteria line under the Hr field.

HTH,
 
Back
Top