breaking a date range into weeks

[awstewar]

I have a table of a years worth of data.

date,sales data
1/1/04 , 2
1/2/04 , 10
1/3/04 , 6
.
.
.
7/1/04 , 9

I would like to enter in a date range. Ex) 1/1/04 - 2/1/04
and have the query print out the sum of the sales data by week.
ex) week 1 , 30
week 2 , 50

If any one knows how to do this please help.

 

[raskew]

Lookup the DatePart() function. You can use it to extract the week.
Here’s a sample query based on Northwind’s Orders table.
It groups by week and returns the sum of Freight for the particular week.

SELECT
    DatePart("ww",[OrderDate]) AS WeekNum
  , Sum(Orders.Freight) AS SumOfFreight
FROM
   Orders
WHERE
   (((Orders.OrderDate) Between [enter start date] 
AND
   [enter end date]))
GROUP BY
   DatePart("ww",[OrderDate]);

HTH - Bob