Solved Sorting Crosstab Query

[jack555]

Having a problem while sorting the custom field (YYY-MM). checked many forum posts but looks complex for me. Please help me to sort based on the month and year.

TRANSFORM Count(qryAll.ArrivalMode) AS CountOfArrivalMode
SELECT Format([qryAll].[ArrivalDate],"yyyy/mmm") AS [Month]
FROM qryAll
GROUP BY Format([qryAll].[ArrivalDate],"yyyy/mmm")
ORDER BY Format([qryAll].[ArrivalDate],"yyyy/mmm")
PIVOT qryAll.FirstFacility;

 

[arnelgp]

Use other Order format:

ORDER BY Format([qryAll.[ArrivalDate],"yyyymm")

 

[jack555]

Use other Order format:

ORDER BY Format([qryAll.[ArrivalDate],"yyyymm")

thanks. below is the error received. tried "yyyymmm" also, the same error.

 

[arnelgp]

Then add it to your select statement.

 

[jack555]

Then add it to your select statement.

tried that as well earlier. below is the result but not in ascending order by month.

 

[Gasman]

Use yyyymm

 

[arnelgp]

if you will add it, you will see that it sorts correct:

TRANSFORM Count(qryAll.ArrivalMode) AS CountOfArrivalMode
SELECT Format([qryAll].[ArrivalDate],"yyyy/mmm") AS [Month], Format([qryAll].[ArrivalDate],"yyyy/mm") AS Expr1
FROM qryAll
GROUP BY Format([qryAll].[ArrivalDate],"yyyy/mmm"), Format([qryAll].[ArrivalDate],"yyyy/mm")
ORDER BY Format([qryAll].[ArrivalDate],"yyyy/mm")
PIVOT qryAll.FirstFacility;

if you do not want to show the New Column, Create yet another Simple Select query based on the above query
and sort on Expr1.

 

[jack555]

if you will add it, you will see that it sorts correct:

TRANSFORM Count(qryAll.ArrivalMode) AS CountOfArrivalMode
SELECT Format([qryAll].[ArrivalDate],"yyyy/mmm") AS [Month], Format([qryAll].[ArrivalDate],"yyyy/mm") AS Expr1
FROM qryAll
GROUP BY Format([qryAll].[ArrivalDate],"yyyy/mmm"), Format([qryAll].[ArrivalDate],"yyyy/mm")
ORDER BY Format([qryAll].[ArrivalDate],"yyyy/mm")
PIVOT qryAll.FirstFacility;

if you do not want to show the New Column, Create yet another Simple Select query based on the above query
and sort on Expr1.

this solved my problem. thank you very much for writing the syntax as well.

 

[jack555]

Just to update that a simple method to solve this problem - accidentally found. Sharing if useful to anyone

1. add the field one more time and format it to the desired (in this case "YYYY/MMM")
2. empty the crosstab box ( nothing to select- no "row heading" or "column heading"), leave it blank.
3. sort it.

This field doesn't appear in the query results but sorting the values which are formatted to "YYYY/MMM"