Count and sum per month in a query

[Radhika]

I have a query that I would like to make from a table that contains four
fields:
1. Date
2. # Hours worked
3. Procedure non-time based units
4. Time-based Units

I want the query to 'Count' the number of entries '# Hours worked' and take
the 'sum' of 'Procedure non-time based units' and 'Time base Units'
separately.

I am able to do this if I do not include Date in the query. However, I would
like to do this for each month, e.g. January, February, etc..The 'Date' field
contains actual dates 1/1/2009, 1/2/2009 in consecutive order for the whole
year.

How can I go about doing this?

Thank you,
Radhika

 

[John W. Vinson]

I have a query that I would like to make from a table that contains four
fields:
1. Date
2. # Hours worked
3. Procedure non-time based units
4. Time-based Units

I want the query to 'Count' the number of entries '# Hours worked' and take
the 'sum' of 'Procedure non-time based units' and 'Time base Units'
separately.

I am able to do this if I do not include Date in the query. However, I would
like to do this for each month, e.g. January, February, etc..The 'Date' field
contains actual dates 1/1/2009, 1/2/2009 in consecutive order for the whole
year.

How can I go about doing this?

Thank you,
Radhika

Create a query based on the table. Include the # hours worked and the units
fields. In a vacant Field cell type

WorkMonth: Format([Date], "yyyy-mm")

to get the month in the format 2008-12, 2009-01 and so on - so it will sort
chronologically.

Change it to a Totals query and group by WorkMonth; use Count on the # hours
worked (or include it twice, and count one and sum the other), and Sum the two
units fields.

 

[Radhika]

Thank you!

I have a query that I would like to make from a table that contains four
fields:
1. Date
2. # Hours worked
3. Procedure non-time based units
4. Time-based Units

I want the query to 'Count' the number of entries '# Hours worked' and take
the 'sum' of 'Procedure non-time based units' and 'Time base Units'
separately.

I am able to do this if I do not include Date in the query. However, I would
like to do this for each month, e.g. January, February, etc..The 'Date' field
contains actual dates 1/1/2009, 1/2/2009 in consecutive order for the whole
year.

How can I go about doing this?

Thank you,
Radhika

Create a query based on the table. Include the # hours worked and the units
fields. In a vacant Field cell type

WorkMonth: Format([Date], "yyyy-mm")

to get the month in the format 2008-12, 2009-01 and so on - so it will sort
chronologically.

Change it to a Totals query and group by WorkMonth; use Count on the # hours
worked (or include it twice, and count one and sum the other), and Sum the two
units fields.