Laurent Laurent - 27 days ago 15
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)

enter image description here

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;
Comments