Crosstab Column Sorting

[Paul H]

I have this crosstab query.

TRANSFORM Sum(Data) AS SumOfData
SELECT MACHINE, Shift
FROM tblTopTen
GROUP BY MACHINE, Shift_Order, Shift
ORDER BY Shift_Order, Rank
PIVOT Rank;

When I use Rank as my column header and sort ascending on it, it works perfectly except my Column Headers are no descriptive, i.e., 1, 2, 3 ... 10. This is the order I need it to sort in, but I also need Reason, the text description of the data, i.e., Broken, Out of Order, Paper Jam etc. When I do the the sort is off. I can't combine Rank and Text because it convert Rank to a string and sort incorrectly, i.e. 1-Broken, 10-Out_of_Order, 2-Paper Jam. The rank and reasons are dynamic, so I can force it in query design.

I hope I've explained myself.

[June7]

The Rank would have to be formatted with leading zeros, like 01-Broken, 02-PaperJam, 10-Out_of_Order, then the alpha sort will work.

[Paul H]

Thanks, great tip.