SQL sorting problem

[Adam]

Hi All,

I've finally managed to create a great crosstab query from a union
query, but I want to sort a field in there in a custom way

I have records under the 'Category' field called "I/C Presented","I/C
Answered","PCA 20", "PCA 40".

Now I want to sort the records in the way I've written them above,
however if I choose to sort the records by Category in Ascending order
"I/C Answered" appears at the top.

Below is a copy of the SQL used in my crosstab query:

----------------
TRANSFORM Max(qryUnionTest.Measurement) AS MaxOfMeasurement
SELECT qryUnionTest.Teams, qryUnionTest.Category,
Max(qryUnionTest.Measurement) AS [Total Of Measurement]
FROM qryUnionTest
GROUP BY qryUnionTest.Teams, qryUnionTest.Category
ORDER BY qryUnionTest.Teams, qryUnionTest.Category
PIVOT Format([MeasurementDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
----------------

Now under ORDER BY can I add:

ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
20", "PCA 40") ?

Many Thanks

Adam

 

[John Vinson]

Now under ORDER BY can I add:

ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
20", "PCA 40") ?

No; but you can use

ORDER BY Switch([Category] = "I/C Presented", 1,
[Category] = "I/C Answered", 2,
[Category] = "PCA 20", 3,
[Category] = "PCA 40, 4,
True, 5)


John W. Vinson[MVP]

 

[Adam]

Brilliant, thank you.

Now under ORDER BY can I add:

ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
20", "PCA 40") ?

No; but you can use

ORDER BY Switch([Category] = "I/C Presented", 1,
[Category] = "I/C Answered", 2,
[Category] = "PCA 20", 3,
[Category] = "PCA 40, 4,
True, 5)


John W. Vinson[MVP]