Solved Sorting Crosstab Query (1 Viewer)

jack555

Member
Local time
Today, 16:29
Joined
Apr 20, 2020
Messages
93
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.
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:29
Joined
May 7, 2009
Messages
19,175
Use other Order format:

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

jack555

Member
Local time
Today, 16:29
Joined
Apr 20, 2020
Messages
93
Use other Order format:

ORDER BY Format([qryAll.[ArrivalDate],"yyyymm")
thanks. below is the error received. tried "yyyymmm" also, the same error.

1601809462665.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:29
Joined
May 7, 2009
Messages
19,175
Then add it to your select statement.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:29
Joined
May 7, 2009
Messages
19,175
if you will add it, you will see that it sorts correct:
Code:
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.
 
Last edited:

jack555

Member
Local time
Today, 16:29
Joined
Apr 20, 2020
Messages
93
if you will add it, you will see that it sorts correct:
Code:
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

Member
Local time
Today, 16:29
Joined
Apr 20, 2020
Messages
93
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"

1604208077791.png
 

Users who are viewing this thread

Top Bottom