Summing By Week

[carl]

My data looks like so:

actdate underlying sales
7/1/09 AAPL 244540
7/2/09 AAPL 195320
7/6/09 AAPL 313196
7/7/09 AAPL 293018
7/8/09 AAPL 384492
7/9/09 AAPL 223600
7/10/09 AAPL 330554
7/13/09 AAPL 328426
7/14/09 AAPL 244178
7/15/09 AAPL 419988
7/16/09 AAPL 300352
7/17/09 AAPL 567876
7/20/09 AAPL 430582
7/21/09 AAPL 639942
7/22/09 AAPL 763672
7/23/09 AAPL 323910
7/24/09 AAPL 316846
7/27/09 AAPL 252306
7/28/09 AAPL 211350
7/29/09 AAPL 204352
7/30/09 AAPL 495328
7/31/09 AAPL 297090

Is there a way to sum sales by week ?

Thank you in advance.

 

[John Spencer]

SELECT Format(actDate,"yyyy-ww") as WeekNumber
, AAPLField, Sum(Amount) as Total
FROM SomeTable
GROUP BY Format(actDate,"yyyy-ww"), AAPLField

Since you haven't told us how you want to determine the week that is the
simplest way to do this.

Or you might want to use an expression like the following to calculate the
first day of the week. This one calculates the first day based on Sunday
DateAdd("d",1-WeekDay(ActDate),ActDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County