Calculate/group by week

S

Sheryl Bradley

I have a year's worth of data.

My fields are Area, Price, Date.

I want the Sum(Price), Count, and Average(Price) Grouped
by Area, then Grouped by Week.

The part I'm stumped on is how to generate a separate
Sum, Count, and Average by week for an entire year's
worth of data without writing VBA code to do so.

Any help is very much appreciated!

Thank You!
Sheryl Bradley
 
J

John Vinson

I have a year's worth of data.

My fields are Area, Price, Date.

I want the Sum(Price), Count, and Average(Price) Grouped
by Area, then Grouped by Week.

The part I'm stumped on is how to generate a separate
Sum, Count, and Average by week for an entire year's
worth of data without writing VBA code to do so.
Click to expand...

Add a calculated field to the query

WeekNo: DatePart("ww", [Date])

and group by it.
 
M

Michel Walsh

Hi,


The group by accepts computed expression. DatePart("ww",
dateTimeArgument) returns the week number.


GROUP BY Year(datetime), DatePart("ww", dateTime), Area


will group by week (and year) and by area.




Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Beautiful ... thanks! :)
-----Original Message-----
I have a year's worth of data.

My fields are Area, Price, Date.

I want the Sum(Price), Count, and Average(Price) Grouped
by Area, then Grouped by Week.

The part I'm stumped on is how to generate a separate
Sum, Count, and Average by week for an entire year's
worth of data without writing VBA code to do so.
Click to expand...

Add a calculated field to the query

WeekNo: DatePart("ww", [Date])

and group by it.


.
Click to expand...
 
S

Sheryl Bradley

Thanks a million ... !! :)
-----Original Message-----
Hi,


The group by accepts computed expression. DatePart ("ww",
dateTimeArgument) returns the week number.


GROUP BY Year(datetime), DatePart("ww", dateTime), Area


will group by week (and year) and by area.




Hoping it may help,
Vanderghast, Access MVP






.
Click to expand...
 
Top