Grouping by weeks

[Matt]

Hi All,

I have the following situation:
I admin a metrics database that includes an attendance piece that
collects hours worked. From this data I show overtime as any amount
of hours over 40 for a particular week.

To do this I have created a table with 4 fields ... Date, week, month,
year. This way I can join on the Date field and then group on year,
week and month. The problem is I must enter data a few times a year
so that this method will continue to work.

My question:
Is there a built in function to handle something like this. I am sure
I could create a function to do this, but was curious as to how others
handle this. What do you guys do in this situation?

[John Winterbottom]

Create a static table populated with all the date buckets or slices you
will ever need and then join to this table in your queries

Matt wrote:

Hi All,

I have the following situation:
I admin a metrics database that includes an attendance piece that
collects hours worked. From this data I show overtime as any amount
of hours over 40 for a particular week.

To do this I have created a table with 4 fields ... Date, week, month,
year. This way I can join on the Date field and then group on year,
week and month. The problem is I must enter data a few times a year
so that this method will continue to work.

My question:
Is there a built in function to handle something like this. I am sure
I could create a function to do this, but was curious as to how others
handle this. What do you guys do in this situation?

[Matt]

Thanks to both of you ... I am actually doing what John had suggested,
but I like the thought proposed by Larry.

I knew about the datepart function, but did not do enough research to
find the week argument.

[Larry Linson]

"John Winterbottom" <jo*****@rogers.cawrote

An added bonus of the bucket table idea is that it
fills in the gaps, so that time slices for which no
data exist are still reported.

I have used a similar construct for the purpose you describe, but it was not
clear this was a requirement.

As I usually have an idea of the environment for which I am designing a
solution, I am less focused, perhaps, on having a universally-applicable
approach.

This is one of those cases where a stored procedure might be useful if using
an Access client to a server DB.

(P.S. A try at communicating by e-mail to the address above was
unsuccessful.)

Larry Linson
Microsoft Access MVP