By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Grouping by weeks

P: n/a
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?

Aug 6 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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?
Aug 6 '07 #2

P: n/a
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.

Aug 6 '07 #3

P: n/a
"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
Aug 7 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.