Group by date on employeeID

J

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.
 
M

Marshall Barton

J.J. said:
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)
 
J

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.J.

Thank you so much. It worked!

Marshall Barton said:
Try using a critrtia something like:

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top