Grouping data by week but Mon to Sun

[Help.Chris]

Hi All,

Thanks for any help in advance, I have some data that needs grouping into weekly totals, but based on Monday to Sunday, instead of the default Sun-Sat used by the datepart function.

The data is basically

Date Pax Cost
1/5/03 3 £300
8/5/03 5 £500
12/5/03 2 £199

Obviously there is a lot more data, this is simplified, does anybody have any idea how to achieve this?

It would also be really usefull to state the dates of the week period, ie 9/6/03 - 15/6/03.

Please suggest solutions, i am out of ideas how to accomplish this.

Thanks all

Chris

 

[Jewellissa]

Actually I just came up with a neat way to solve this. My workplace the weeks are Sat to Sat and it is easier to input data as week numbers. But then you have problems with years so here is what I do.

At the beginning of every year the manager I am writing my program for has to specify a few variables and one them is to specify (for my program) the first Sat of the year. The I use the AddDate function to add 7*(WeekNum-1) + (First Sat) and Voila... week ending date!!!

Now all I need to do is build a query that say... seraches for all employees off Week 25.. query the table for all employees of [(First Sat) + (WeekNum-1)*7] - [[(First Sat) + (WeekNum-1)*7]-7]

I hope this was what you were asking...
Jewelz