Grouping on weekly basis

[aarondewberry]

All
See below SQL and results. What i want to do is to be able to group my query on a weekly basis (7 day blocks) instead of single days

Desired Results
DateReceived CountOfDateReceived
01/05/2007 - 05/05/07 811
........

Original SQL
SELECT [WorkTracking Main].DateReceived, Count([WorkTracking Main].DateReceived) AS CountOfDateReceived
FROM [WorkTracking Main]
GROUP BY [WorkTracking Main].DateReceived
HAVING ((([WorkTracking Main].DateReceived) Between #4/30/2007# And #6/3/2007#));

Original Results
DateReceived CountOfDateReceived
01/05/2007 156
02/05/2007 169
03/05/2007 149
04/05/2007 228
05/05/2007 109
09/05/2007 107
10/05/2007 84
11/05/2007 128
12/05/2007 168
15/05/2007 146
16/05/2007 192
17/05/2007 165
18/05/2007 123
19/05/2007 79
.....

 

[Remou]

How about:

[tt]SELECT Format([DateReceived],"yy ww") As YrWeekNo, Count([WorkTracking Main].DateReceived) AS CountOfDateReceived
FROM [WorkTracking Main]
GROUP BY Format([DateReceived],"yy ww")[/tt]

 

[dhookom]

I'm not sure why, but the WW in the format might return just a single digit like 9 rather than 09 as expected. This would muck up the sorting in the query so that 12 would come before 2,3,4,...

One solution to get the first date of a week is to use:
WeekOf: DateAdd("d",-Weekday([DateReceived])+1,[DateReceived])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aarondewberry]

Brilliant. Thanks for your help guys.