Year and Week Totals

[Jim]

I have a table that has sales data stored for shipments on calendar days. I
have built a query to total the shipments by year and then by week. In the
query builder I am using:
Year: DatePart("yyyy",public_ordhead!date_ship)
Week: DatePart("ww",public_ordhead!date_ship)
The resulting data is presenting the data correctly by year, then week. The
challenge is that I would like partial weeks to total together.
Specifically, there are a few days in week 53 for last year and a few days
for week one this year. Is there a way to total by full calendar week?
Thank you for your assistance.
Jim

 

[John Spencer]

You can try to force the date to be the beginning of the week (Assuming
Sunday as the beginning of the week you could use:

Year:
Year(DateAdd("d",1-Weekday(public_ordhead!date_ship),public_ordhead!date_ship)
Week:
DatePart("ww",DateAdd("d",1-Weekday(public_ordhead!date_ship),public_ordhead!date_ship))

You might also take a look at the additional arguments for DatePart().

DatePart("ww",[Date_Ship],,3) will return 52 for Jan 1, 2009. Although
there is a small problem in that it will return 2009 for the year.

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

 

[Jim]

John,
Thanks for the response. That did combine the last partial week of the year
and the first partial week of the next year just as I had hoped. The week is
designmated as week "53" and the first week of the new year is "2" but that
isn't that big of deal.
Thanks again.
Jim

You can try to force the date to be the beginning of the week (Assuming
Sunday as the beginning of the week you could use:

Year:
Year(DateAdd("d",1-Weekday(public_ordhead!date_ship),public_ordhead!date_ship)
Week:
DatePart("ww",DateAdd("d",1-Weekday(public_ordhead!date_ship),public_ordhead!date_ship))

You might also take a look at the additional arguments for DatePart().

DatePart("ww",[Date_Ship],,3) will return 52 for Jan 1, 2009. Although
there is a small problem in that it will return 2009 for the year.

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

I have a table that has sales data stored for shipments on calendar days. I
have built a query to total the shipments by year and then by week. In the
query builder I am using:
Year: DatePart("yyyy",public_ordhead!date_ship)
Week: DatePart("ww",public_ordhead!date_ship)
The resulting data is presenting the data correctly by year, then week. The
challenge is that I would like partial weeks to total together.
Specifically, there are a few days in week 53 for last year and a few days
for week one this year. Is there a way to total by full calendar week?
Thank you for your assistance.
Jim