Group by date on employeeID

[J.J.]

A previous contributor and I were both led astray earlier so I will modify my
question to make it easily understood. The structure of the statement has
been causing the problem. I have a table with employeeID's and workDates and
hoursWorked. Along with other data the table looks like:

employeeID workDate hoursWorked
_________________________________
001 12/28/2008 8
001 01/02/2009 8
003 01/02/2009 8
001 01/03/2009 4
002 01/03/2009 6
001 02/03/2009 10 etc.

What I would like to do is to sum the hours worked in the previous month for
each employee in a query that returns one value per employeeID. Thank you
for your kind assistance.

 

[Marshall Barton]

A previous contributor and I were both led astray earlier so I will modify my
question to make it easily understood. The structure of the statement has
been causing the problem. I have a table with employeeID's and workDates and
hoursWorked. Along with other data the table looks like:

employeeID workDate hoursWorked
_________________________________
001 12/28/2008 8
001 01/02/2009 8
003 01/02/2009 8
001 01/03/2009 4
002 01/03/2009 6
001 02/03/2009 10 etc.

What I would like to do is to sum the hours worked in the previous month for
each employee in a query that returns one value per employeeID. Thank you
for your kind assistance.

Try using a critrtia something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0)

 

[Jeff Boyce]

Let's see if I can paraphrase your request, based on the structure you
describe...

You want to see the sum of [hoursWorked] per [employeeID], where [workDate]
is in the month preceding the current month.

If that's accurate, one approach would be to use a Totals query, grouping by
[employeeID], summing on [hoursWorked], and using the following as a
selection criterion for a "Where" selection in the aggregation field of the
query design view under the [workDate] field:

Between DateSerial(Year(Date()), Month(Date())-1, 1) And
DateSerial(Year(Date()), Month(Date()), 0)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J.J.]

Thank you so much. It worked!

Try using a critrtia something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),0)

 

[John W. Vinson]

sum the hours worked in the previous month

I see that Jeff and Marshall interpreted this phrase differently; it *is*
ambiguous.

Marshall assumed that you meant the "previous month" to be the dates January
12 through February 12. Jeff assumed that you meant January 1 through January
31. Both are reasonable interpretations of the phrase - which did you intend?
Choose the appropriate answer, they'll both work!