Group by Weeks - Year Wrap

[Charles D Clayton Jr]

A2K
I need to group data by weeks. I can do this with either the format
([dt], "ww") or the datepart("ww", [dt]) functions. My problem is
that I want them to be in descending order from latest to earliest and
since they go over the year mark the weeks start back at 1 even though
it is a year later. I tried adding the year to the function
(datepart("yyyy", [dt])&" "& datepart("ww", [dt]) but that gives me a
different problem. While it will sort according to the year, it
treats the weeks as a string and has 10,11,12, before 2,3,4,5. Does
anybody have any suggestions on how to accomplish this?

Thanks,

Charles D Clayton Jr

 

[Guest]

Since no-one has answered yet, I thought I'd make a suggestion

Could you not use the len function as so

DatePart("yyyy", [dt]) & IIf(Len(DatePart("ww",[dt]))=1,"0") & DatePart("ww", [dt]

This was the first soultion that came to mind - there may be a neater way that someone else knows

M

----- Charles D Clayton Jr wrote: ----

A2
I need to group data by weeks. I can do this with either the forma
([dt], "ww") or the datepart("ww", [dt]) functions. My problem i
that I want them to be in descending order from latest to earliest an
since they go over the year mark the weeks start back at 1 even thoug
it is a year later. I tried adding the year to the functio
(datepart("yyyy", [dt])&" "& datepart("ww", [dt]) but that gives me
different problem. While it will sort according to the year, i
treats the weeks as a string and has 10,11,12, before 2,3,4,5. Doe
anybody have any suggestions on how to accomplish this

Thanks

Charles D Clayton J

 

[Michel Walsh]

Hi,


An alternative is :



SELECT ..., LAST(dt)
FROM ...
GROUP BY ...
ORDER BY LAST(dt) DESC


You can use MAX, or MIN, instead of LAST, in this case, the idea being to
get a value "in" the week... and we just use that value to ORDER BY
appropriately.


Hoping it may help,
Vanderghast, Access MVP