Calculate/group by week

  • Thread starter Thread starter Sheryl Bradley
  • Start date Start date
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
 
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.
 
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
 
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...
 
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...
 
Back
Top