Total by Week

[Craig]

I want to design a query from a data table which will
give me a total by week which will include the beginning
date of that week. Access has only day month and year
defualts. Does anyone know how can formualate by week?

Craig

 

[Michel Walsh]

Hi,


GROUP BY DatePart("ww", MyDate)


will group by week number.


To get the Sunday of the same week of a given day, x :

x - DatePart("w", x)

( I assumed weeks are from Sunday to Saturday).

So:



SELECT LAST(myDate)-DatePart("w", LAST(myDate)), SUM( myData)
FROM myTable
GROUP BY DatePart("ww", myDate)



should do.


Hoping it may help,
Vanderghast, Access MVP