Month Query

[betheball]

I am guessing this has been done many times, but can't quite get it right. Each day I input to the db the date (myDate), the number of items produced (Items) on that date and the number of hours worked on that date (myHours). What I want to do is create a query that will bring back the total hours, total items, items per hour by month. So if my table has the following three records:

Date Items Hours
2/21 21 3
2/24 20 4
2/25 13 2

I want my query to look something like this:

Month Items Hours Rate
2 54 9 6

Now there are other dates in the db so ideally my final output would look like this:

Month Items Hours Rate
2 54 9 6
3 60 15 4

and so on.

Any thoughts on how to write this query???

 

[pbaldy]

Try something like this (guessing on rate):

SELECT Month(DateField) AS Monthly, Sum(Items) AS TotalItems, Sum(Hours) AS TotalHours, TotalItems/TotalHours AS Rate
FROM TableName
GROUP BY Month(DateField)

 

[betheball]

Thanks Paul. I forgot one small part of this equation. My date field is InputDate. While I want to group by month, I need to filter by InputDate, i.e., HAVING InputDate BETWEEN 1/1/04 and 6/1/04. When I do that, I get the infamous aggregate function error:

You tried to execute a query that does not include the specified expression 'InputDate Between #1/1/2001# And #1/1/2001# as part of an aggregate function.

 

[pbaldy]

Try it as a WHERE instead of HAVING; it's more appropriate to what you want anyway, it sounds like. Worked in a test I tried.