Group by Week for any given start date

[royalrochelle]

I apologize in advance if there is a similar post already solved up here but I have been searching for days and can't find what I am looking for.

I am working on a database that is gathering information for people who are on a study. Each individual on the study is supposed to make 4 calls a day so there should be 28 calls for a week. If the person makes all 28 calls for a week they get a bonus. They can get a bonus every week they make 28 calls. datepart("ww",date()) is very close to what I want but this numbers the weeks by calendar year. Each individual can have a different start date. I would like the query to group by weeks but the week starting with the individual's start date. So if the person starts today I would like it to look something like this:

Week Week Range Data


Week 1 2/15/12 - 2/21/12 20
Week 2 2/22/12 - 2/28/12 28

I really don't need to see the date range but I just put that there so you can see what I am looking for. Is this possible? Someone please help before I pull all the hair out of my head!!

[aytee111]

See Query8 in the attached db.

[June7]

You will have us all pulling our hair out by the roots! Every person will have a different first day of the week dependent on their start day. The DatePart function has an argument to specify the first day of week. Use WeekDay function to determine the day of week of the start date and use that value to set the first day of week for the DatePart argument. Like:

DatePart("ww", Date(), WeekDay([StartDate]))

Could just prorate their first week so everyone is on the same weekly schedule.

[royalrochelle]

Thank you SOOOO much! This worked! Thank you both for taking time out of your day to help me! I really appreciate it.