Query that sums by week of month

[Secret Squirrel]

I'm trying to create a query where I can total an employees hours worked for
each week of a specified month. How can I create the query to total their
hours and then show the total by each week of the month?

Example:

EmployeeName W/E 1/10/09 W/E 1/17/09 W/E 1/24/09 W/E 1/31/09

John Doe 32.00 40.00 40.00
40.00

I have a table called tblTimeClock that lists a record for each day of the
week and the hours they worked that day. So what I would need to do is sum
them up and put them into the columns above depending on the date. Then I
want to just select a month and have it create the "W/E" columns for that
month. Any ideas?

 

[Arvin Meyer [MVP]]

Take a look at the Partition() function in VBA help check the example out.
Although you can't partition by a date field, you can group your dates by
week and get a total, then using a partition function spread them out
horizontally.

 

[Secret Squirrel]

Hi Arvin,

I think I understand the partition function but how do I group my dates by
week and also be able to choose a month and have it group them based on the
month I select?

 

[Arvin Meyer [MVP]]

You'll need an alias column for each in your queries:

WorkWeek: Week([DateField])

WorkMonth: Month([DateField])

Use each of those in a Totals Query which groups by week (or month) and sums
by the duration (hours worked)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Secret Squirrel]

I'm getting an error saying "undefined function 'week' in expression".

You'll need an alias column for each in your queries:

WorkWeek: Week([DateField])

WorkMonth: Month([DateField])

Use each of those in a Totals Query which groups by week (or month) and sums
by the duration (hours worked)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I think I understand the partition function but how do I group my dates by
week and also be able to choose a month and have it group them based on
the
month I select?

 

[Arvin Meyer [MVP]]

Sorry that was a function I wrote, and it probably isn't a good idea to use
the name Week for it. It's really simple though, use:

WorkWeek: DatePart("ww", [DateField])

That will give you the week number of the year.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm getting an error saying "undefined function 'week' in expression".

You'll need an alias column for each in your queries:

WorkWeek: Week([DateField])

WorkMonth: Month([DateField])

Use each of those in a Totals Query which groups by week (or month) and
sums
by the duration (hours worked)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I think I understand the partition function but how do I group my dates
by
week and also be able to choose a month and have it group them based on
the
month I select?

:

Take a look at the Partition() function in VBA help check the example
out.
Although you can't partition by a date field, you can group your dates
by
week and get a total, then using a partition function spread them out
horizontally.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message I'm trying to create a query where I can total an employees hours
worked
for
each week of a specified month. How can I create the query to total
their
hours and then show the total by each week of the month?

Example:

EmployeeName W/E 1/10/09 W/E 1/17/09 W/E 1/24/09 W/E
1/31/09

John Doe 32.00 40.00
40.00
40.00

I have a table called tblTimeClock that lists a record for each day
of
the
week and the hours they worked that day. So what I would need to do
is
sum
them up and put them into the columns above depending on the date.
Then
I
want to just select a month and have it create the "W/E" columns for
that
month. Any ideas?