Group By Week

[zyus]

My sample data

Colldt
01-09-2009
03-09-2009
01-10-2009
02-10-2009
14-10-2009
17-10-2009

In a query i want to group the colldt by following

week1-7
week8-14
week15-21
week22-31

The grouping will be based on day in colldt, say 01-10-2009, week1-7 and
17-10-2009, week15-21 and so forth.

Thanks

 

[Tom Wickerath]

Hi Zyus,

Perhaps this will help? This example is for the Orders table in the sample
Northwind database:

WeekNum: Int(DatePart("d",[OrderDate])/7)+1
or
WeekNum: "Week " & Int(DatePart("d",[OrderDate])/7)+1

Note that this groups OrderDates for all months, with WeekNum values that
range between 1 to 5. Since your dates appear to me formatted as dd-mm-yyyy,
you may [I'm not sure] have to format the Colldt field as shown in Allen
Browne's article, here:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[zyus]

Hi Tom,

Thanks for your response.

For date 7, 14 , 21 ....it will fall under next grouping...say 7 will group
as week 2 and 14 as week 3...is there any trick to put 7 as week 1 &...

Hi Zyus,

Perhaps this will help? This example is for the Orders table in the sample
Northwind database:

WeekNum: Int(DatePart("d",[OrderDate])/7)+1
or
WeekNum: "Week " & Int(DatePart("d",[OrderDate])/7)+1

Note that this groups OrderDates for all months, with WeekNum values that
range between 1 to 5. Since your dates appear to me formatted as dd-mm-yyyy,
you may [I'm not sure] have to format the Colldt field as shown in Allen
Browne's article, here:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

My sample data

Colldt
01-09-2009
03-09-2009
01-10-2009
02-10-2009
14-10-2009
17-10-2009

In a query i want to group the colldt by following

week1-7
week8-14
week15-21
week22-31

The grouping will be based on day in colldt, say 01-10-2009, week1-7 and
17-10-2009, week15-21 and so forth.

Thanks