How to sort columns in a Crosstab Query

  • Thread starter Thread starter me_nc
  • Start date Start date
M

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:)
 
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 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


mscertified said:
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

[email protected] said:
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:)
Click to expand...
Click to expand...
 
Back
Top