SQL Question
Crosstab query sort by year and month
I have the following SQL Crosstab query (Microsoft Access)
TRANSFORM Sum(tbEcritureDetail.iMontantCHF) AS SumOfiMontantCHF
SELECT Format([dValeur],"mm\.yyyy") AS Mois, Sum(tbEcritureDetail.iMontantCHF) AS Ecart
FROM tbTypeCategories INNER JOIN ((tbCategories INNER JOIN (tbSousCategorie INNER JOIN (tbEcritureHeader INNER JOIN tbEcritureDetail ON tbEcritureHeader.KeyPK = tbEcritureDetail.iDocPrincipalRef) ON tbSousCategorie.KeyPK = tbEcritureDetail.iSousCategorie) ON tbCategories.KeyPK = tbSousCategorie.iRefCategorie) INNER JOIN tbComptes ON tbEcritureHeader.iCompteRef = tbComptes.KeyPK) ON tbTypeCategories.KeyPK = tbSousCategorie.iTypeCategorie
WHERE (((tbEcritureHeader.dValeur) Between #1/1/2013# And #12/31/2016#) AND ((tbComptes.sTypeCompte)<>"TITRES"))
GROUP BY Format([dValeur],"mm\.yyyy")
ORDER BY tbTypeCategories.sDescription
PIVOT tbTypeCategories.sDescription;
The way the data is sorted is not exactly what I was expecting (see screenshort)
We can notice that we have January 2013, followed by January 2014, followed by January 2015, etc ...
What I would like to have is January 2013, February 2013, ..., December 2013, January 2014, etc ...
What am I doing wrong in the crosstab query?
Any help would be appreciated. Thanks in advance.
Answer
Try order by Format([dValeur],"mm.yyyy")
TRANSFORM Sum(tbEcritureDetail.iMontantCHF) AS SumOfiMontantCHF
SELECT Format([dValeur],"mm\.yyyy") AS Mois, Sum(tbEcritureDetail.iMontantCHF) AS Ecart
FROM tbTypeCategories INNER JOIN ((tbCategories INNER JOIN (tbSousCategorie INNER JOIN (tbEcritureHeader INNER JOIN tbEcritureDetail ON tbEcritureHeader.KeyPK = tbEcritureDetail.iDocPrincipalRef) ON tbSousCategorie.KeyPK = tbEcritureDetail.iSousCategorie) ON tbCategories.KeyPK = tbSousCategorie.iRefCategorie) INNER JOIN tbComptes ON tbEcritureHeader.iCompteRef = tbComptes.KeyPK) ON tbTypeCategories.KeyPK = tbSousCategorie.iTypeCategorie
WHERE (((tbEcritureHeader.dValeur) Between #1/1/2013# And #12/31/2016#) AND ((tbComptes.sTypeCompte)<>"TITRES"))
GROUP BY Format([dValeur],"mm\.yyyy")
ORDER BY Format([dValeur],"mm\.yyyy")
PIVOT tbTypeCategories.sDescription;
Source (Stackoverflow)