How to sort columns in a Crosstab Query

[me_nc]

I have a Crosstab query that uses a rolling 6 months of week numbers
for column names but will not sort the week numbers in order of the
date.

TRANSFORM Count(test.Status) AS TEST
SELECT test.Status
FROM test
WHERE (((test.Status) Like "N*") AND ((test.Date) Between
(DateAdd("m",-6,Now())) And Now()))
GROUP BY test.Status
PIVOT Format([test].[Date],"ww");

When this is ran between years or at the beginning of a year the week
numbers are order like this:

1,10,11,12,13,14,2,3,4,40,41,42,43,44,45,46,47,48,49,5,50,51,52,6,7,8,9

when I would want it to sort column headers like this

40,41,42,53,44,45,46,47,48,49,50,51,52,1,2,3,4,5,6,7,8,9,10,11,12,13,14

Any suggestions on how to do this? ORDER BY and IN don't seem to work
for my solution.

Thanks Jimbo

 

[mscertified]

You need the query to concatenate the Year onto the front of the week and you
need leading zeros in the week, e.g. week 1 = 01

-Dorian

 

[Duane Hookom]

I would use relative weeks as column headings. There is a "monthly" solution
for this at http://www.tek-tips.com/faqs.cfm?fid=5466. Just change the
expressions to weeks rather than months.
--
Duane Hookom
Microsoft Access MVP

You need the query to concatenate the Year onto the front of the week and you
need leading zeros in the week, e.g. week 1 = 01

-Dorian

I have a Crosstab query that uses a rolling 6 months of week numbers
for column names but will not sort the week numbers in order of the
date.

TRANSFORM Count(test.Status) AS TEST
SELECT test.Status
FROM test
WHERE (((test.Status) Like "N*") AND ((test.Date) Between
(DateAdd("m",-6,Now())) And Now()))
GROUP BY test.Status
PIVOT Format([test].[Date],"ww");

When this is ran between years or at the beginning of a year the week
numbers are order like this:

1,10,11,12,13,14,2,3,4,40,41,42,43,44,45,46,47,48,49,5,50,51,52,6,7,8,9

when I would want it to sort column headers like this

40,41,42,53,44,45,46,47,48,49,50,51,52,1,2,3,4,5,6,7,8,9,10,11,12,13,14

Any suggestions on how to do this? ORDER BY and IN don't seem to work
for my solution.

Thanks Jimbo