1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| SELECT *
FROM [SELECT iif(AFFAIRES.[Transaction aboutie]=-1,"Transactions abouties") AS Libellé, Count(AFFAIRES.[Transaction aboutie]) AS Volume, Count(AFFAIRES.[Transaction aboutie])/(SELECT Count(AFFAIRES.Réfaffaires) AS CompteDeRéfaffaires FROM AFFAIRES) AS Taux, Sum(AFFAIRES.[Montant honoraires HT]) AS CA
FROM AFFAIRES
WHERE (((AFFAIRES.[Transaction aboutie])=True))
GROUP BY AFFAIRES.[Transaction aboutie]
UNION
SELECT iif(AFFAIRES.[Transaction en cours]=-1,"Transactions en cours sur exercice") AS Libellé, iif(Count(AFFAIRES.[Transaction en cours])=0,"-", Count(AFFAIRES.[Transaction en cours])) AS Volume, Count(AFFAIRES.[Transaction aboutie])/(SELECT Count(AFFAIRES.Réfaffaires) AS CompteDeRéfaffaires FROM AFFAIRES) AS Taux, Sum(AFFAIRES.[Montant honoraires HT]) AS CA
FROM AFFAIRES
WHERE (((AFFAIRES.[Transaction en cours])=True)) AND (AFFAIRES.[Signature définitive prévue le]) BETWEEN iif(Date() > DateSerial(Year(Date()), 7, 31),DateSerial(Year(Date()), 8, 1),DateSerial(Year(Date()) - 1, 8, 1)) AND iif(Date() > DateSerial(Year(Date()), 7, 31),DateSerial(Year(Date()) + 1, 7, 31),DateSerial(Year(Date()), 7, 31))
GROUP BY AFFAIRES.[Transaction en cours]
UNION
SELECT iif(AFFAIRES.[Transaction en cours]=-1,"Transactions en cours hors exercice") AS Libellé, iif(Count(AFFAIRES.[Transaction en cours])=0,"-", Count(AFFAIRES.[Transaction en cours])) AS Volume, Count(AFFAIRES.[Transaction aboutie])/(SELECT Count(AFFAIRES.Réfaffaires) AS CompteDeRéfaffaires FROM AFFAIRES) AS Taux, Sum(AFFAIRES.[Montant honoraires HT]) AS CA
FROM AFFAIRES
WHERE (((AFFAIRES.[Transaction en cours])=True)) AND IIF(Date()<DateSerial(Year(Date()), 7, 31),AFFAIRES.[Signature définitive prévue le]>DateSerial(Year(Date()), 7, 31),AFFAIRES.[Signature définitive prévue le]>DateSerial(Year(Date())+1,7,31))
GROUP BY AFFAIRES.[Transaction en cours]
UNION
SELECT iif(AFFAIRES.[Transaction avortée]=-1,"Transactions avortées") AS Libellé, Count(AFFAIRES.[Transaction avortée]) AS Volume, Count(AFFAIRES.[Transaction aboutie])/(SELECT Count(AFFAIRES.Réfaffaires) AS CompteDeRéfaffaires FROM AFFAIRES) AS Taux, Sum(AFFAIRES.[Montant honoraires HT]) AS CA
FROM AFFAIRES
WHERE (((AFFAIRES.[Transaction avortée])=True))
GROUP BY AFFAIRES.[Transaction avortée]
]. AS [%$##@_Alias]
ORDER BY iif(Libellé="Transactions abouties",1,iif(Libellé="Transactions en cours sur exercice",2,iif(Libellé="Transactions en cours hors exercice",3,iif(Libellé="Transactions avortées",4,null)))); |
Partager