Get totals by week?

[Guest]

I can figure out how to report totals, grouped by an Element, for a specific
week:
SELECT Count(tblSAR.SAR_ID) AS CountOfSAR_ID, tblSAR.SAR_Element
FROM tblSAR
WHERE (((tblSAR.SAR_Date_Created) Between #4/1/2006# And #4/7/2006#))
GROUP BY tblSAR.SAR_Element;

I need to provide the counts for each week beginning in April through
August. Is there any way to do this other than run this query 21 times with
different date ranges?

I looked at using a Crosstab query, but can only get that to group by month
(not week).

I could sure use some help!
Thank you,
Judy

 

[Guest]

Add that as another field in the query to return the week number

WeekNum: DatePart("ww",[SAR_Date_Created])

If you want to use it between years, then you need to add the Year to it

WeekNum: DatePart("ww",[SAR_Date_Created]) & "-" &
DatePart("yyyy",[SAR_Date_Created])