Discussion :

[maringot]

Bonjour,

Avec ma BD je gère la facturation de mes clients. J'aimerais créer un tableau comparatif de CA sur 2 années (N/N-1) suivant ce modèle :

Pièce jointe 327767

J'ai créé une requête qui liste le CA par mois

Pièce jointe 327773

Mon problème est que je ne sais pas comment mettre en forme les tableaux à partir de la requête : faire coïncider "avril" avec "4", créer les écarts en valeur et en %...

Merci d'avance pour vos conseils,

[User]

Bonsoir,

Si j'ai bien saisi,
Il faut faire 2 requêtes pour les mensuels et 2 pour les cumuls :
requête 1 : année n
requête 2 : année n-1

Si je pars d'une table toute simple

T_Facture(IdFacture,RefFacture,DateFacture,MontantHT)

J'obtiens la requête [R_CA Mensuel (n)] :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT Year([DateFacture]) AS Annee, Month([DateFacture]) AS Mois, Format([DateFacture],"mmmm") AS LeMois, Sum(T_Facture.MontantHT) AS CA
FROM T_Facture
WHERE (((Year([DateFacture]))=Year(Date())))
GROUP BY Year([DateFacture]), Month([DateFacture]), Format([DateFacture],"mmmm")
ORDER BY Year([DateFacture]), Month([DateFacture]);

la requête [R_CA Mensuel (n-1)] :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT Year([DateFacture]) AS Annee, Month([DateFacture]) AS Mois, Format([DateFacture],"mmmm") AS LeMois, Sum(T_Facture.MontantHT) AS CA
FROM T_Facture
WHERE (((Year([DateFacture]))=Year(Date())-1))
GROUP BY Year([DateFacture]), Month([DateFacture]), Format([DateFacture],"mmmm")
ORDER BY Year([DateFacture]), Month([DateFacture]), Year([DateFacture]);

Et le tableau de CA mensuel dans la requête R_CA Mensuel :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT T_Mois.IndMois, T_Mois.nomMois, [R_CA Mensuel (n)].CA AS [CA (N)], [R_CA Mensuel (n-1)].CA AS [CA (N-1)], [CA (N)]-[CA (N-1)] AS [Ecart en valeur], ([CA (N)]-[CA (N-1)])/[CA (N-1)] AS [Ecart en %]
FROM (T_Mois LEFT JOIN [R_CA Mensuel (n-1)] ON T_Mois.IndMois = [R_CA Mensuel (n-1)].Mois) LEFT JOIN [R_CA Mensuel (n)] ON T_Mois.IndMois = [R_CA Mensuel (n)].Mois;

Et pour les cumuls :

J'obtiens la requête [R_CA Cumul (n)] :

Code sq :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT DISTINCT Year([DateFacture]) AS Annee, Month([DateFacture]) AS Mois, Format([DateFacture],"yyyy/mm") AS LeMois, (select Sum(MontantHT) From T_Facture T1 where Year(T1.DateFacture) = Year(T_Facture.DateFacture) and (Month(T1.DateFacture)<=Month(T_Facture.DateFacture));) AS CumulCA
FROM T_Facture
WHERE (((Year([DateFacture]))=Year(Date())));

et la requête [R_CA Cumul (n-1)] :

Code sq :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT DISTINCT Year([DateFacture]) AS Annee, Month([DateFacture]) AS Mois, Format([DateFacture],"yyyy/mm") AS LeMois, (select Sum(MontantHT) From T_Facture T1 where Year(T1.DateFacture) = Year(T_Facture.DateFacture) and (Month(T1.DateFacture)<=Month(T_Facture.DateFacture));) AS CumulCA
FROM T_Facture
WHERE (((Year([DateFacture]))=Year(Date())-1));

Enfin, le tableau des CA cumulés dans la requête [R_CA Cumul] :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT T_Mois.IndMois, T_Mois.nomMois, [R_CA Cumul (n)].CumulCA AS [CA (N)], [R_CA Cumul (n-1)].CumulCA AS [CA (N-1)], [CA (N)]-[CA (N-1)] AS [Ecart en valeur], ([CA (N)]-[CA (N-1)])/[CA (N-1)] AS [Ecart en %]
FROM (T_Mois LEFT JOIN [R_CA Cumul (n)] ON T_Mois.IndMois = [R_CA Cumul (n)].Mois) LEFT JOIN [R_CA Cumul (n-1)] ON T_Mois.IndMois = [R_CA Cumul (n-1)].Mois;

Ci-joint l'exemple...

Cdlt,

[Invité]

Bonjour,
Le Having concerne le group by , having count(*)>3 par exemple tout filtre qui ne le concerne pas sera fait par le where, ça soulage le group by!

Select
From
where
Group by
Having
Order by

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT Year([DateFacture]) AS Annee, Month([DateFacture]) AS Mois, Format([DateFacture],"mmmm") AS LeMois, Sum(T_Facture.MontantHT) AS CA
FROM T_Facture
Where (((Year([DateFacture]))=Year(Date())))
 
GROUP BY Year([DateFacture]), Month([DateFacture]), Format([DateFacture],"mmmm")
ORDER BY Year([DateFacture]), Month([DateFacture]);

Personnellement je n'aime pas faire des requête avec jointure sur deux exercices! Là il est fort à parier que le mois de janvier sera présent dans les deux!

Moi je fais une requête qui prend les deux années et je fais un iif(year(date)-1=year[laDate]),[ca],0) as Camoins1,iif(year(date)=year[laDate]),[ca],0) as CaEncour

[User]

Bonjour,

Bonjour,
Le Having concerne le group by , having count(*)>3 par exemple tout filtre qui ne le concerne pas sera fait par le where, ça soulage le group by!

Select
From
where
Group by
Having
Order by

Merci bien, mais je suis au courant, c'est simplement que j'utilise le QBE d'Access et que j'ai simplement glissé le critère sous le champ comportant un group by, ça ma paru plus simple que d'ajouter une colonne pour ce champ avec un Où

Concernant les mois manquant, la aussi j'y avait pensé, mais ne voulant pas alourdir les choses avec un table supplémentaire, du coup j'y suis un peu contraint : j'ai donc ajouté une table dans la requête finale avec des jointures gauches sur le mois pour garder les 12 mois de l'année.

Cdlt,

[Invité]

Bonjour,

Code Sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Month([Date]) AS Mois, Sum(IIf(Year([date])=Year(Date())-1,[ca],0)) AS [N-1], Sum(IIf(Year([date])=Year(Date()),[ca],0)) AS N FROM Table1
where Year([date])>Year(date()) -2
GROUP BY Month([Date]);

[User]

Oui, j'ai deja eu l'occasion de faire ce type de requêtes, mais j'ai toujours l'impression qu'on perd en lisibilité quand on cherche à optimiser, je voulais plusieurs requete pour decomposer le probleme en plusieurs etapes...

[maringot]

Bonjour User,

Merci pour ce super boulot, je n'avais pas poussé la réflexion aussi loin.

Donc j'ai adapté vos requêtes à ma base. Concernant les 3 premières pas de souci ça fonctionne. Mais à partir des requêtes de Cumul, ça cafouille...
J'ai essayé d'adapter la formule mais j'ai un message "Erreur de syntaxe" et je n'ai pas compris à quoi faisait référence le "T1". Donc voici à quoi cela ressemble :



Les 3 premières formules fonctionnent et le message d'erreur porte sur la 4ème. Sauriez-vous m'expliquer d'où vient le problème ?
Avec mes remerciements

[User]

Bonjour,

Il faudrait mettre entre crochets le nom de la requête dans la clause From de la sous-requête.

Pouvez-vous poster votre base sans données confidentielles se sera plus simple ?

Cdlt,

[maringot]

Bonjour user,

J'ai écris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CumulCA: (select Sum(SommeDeSommeDeTotal HTDFac) From [RQ-TOTAL TTC FAC] T1 where Year(T1.DateFac) = Year(T-Facture FI.DateFac) and (Month(T1.DateFac)<=Month(T-Facture FI.DateFac));)

mais ça ne change rien, toujours "erreur de syntaxe".

Quant à la BD ça m'est difficile de la communiquer car c'est celle d'une SA.

[User]

Rassemble la table "T-Facture FI" et la requête "RQ-TOTAL TTC FAC" dans une requête avec liaison sur les champs "IDFactureFI" et "ID_Facture Fi", cette requête tu la nommes "R_Facture".

Ensuite tu base la requête "R_CA Cumul (n)" sur cette requête "R_Facture".

Après le champ de cumul donnerait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CumulCA: (select Sum(SommeDeSommeDeTotal HTDFac) From [R_Facture] T1 where Year(T1.DateFac) = Year([R_Facture].DateFac) and (Month(T1.DateFac)<=Month([R_Facture].DateFac));)

Sinon, c'est pas compliqué de faire un clône de ta base sans les données et de joindre la base comme ça, histoire de voir ou ça bug...

Cdlt,

[maringot]

Bonjour user,

Voici la BD simplifiée que j'utilise Pièce jointe 328965.
Dans les requêtes de "cumul" il n'y a pas la formule car comme il y une erreur de syntaxe elle ne s'enregistre pas.

[User]

Bonjour,

J'ai créé une requête intermédiaire nommée R_Facture.

Ci-joint le fichier.

[maringot]

Suite à votre dernière modif, les requêtes cumuls fonctionnent.

Je vous remercie pour le temps que vous avez consacré à ma demande.

Bien cordialement,
Maringot

[maringot]

J'ai oublié de vous posez une dernière question, l'exercice comptable débute en avril, comment puis-je faire pour que les mois soient classés d'avril à mars dans les tableaux ?

[User]

J'ai oublié de vous posez une dernière question, l'exercice comptable débute en avril, comment puis-je faire pour que les mois soient classés d'avril à mars dans les tableaux ?

Je ne comprend par trop ce revirement par rapport a ce que vous avez exposé jusque là

Les mois ne sont pas dans la même année, et les cumuls à l'année n sont donc en fait à cheval sur 2 années.

J'ai ajouté un champ numéro dans la table T_Mois pour classer les mois suivant cet ordre.

Pour le reste, le problème vient des conditions à appliquer dans le critère portant sur les dates, si j'ai bien compris, par exemple :

pour l'année n :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Format([DateFacture];"aaaa/mm") Entre (Année(Date())) & "/04" Et (Année(Date())+1) & "/03"

et pour l'année n-1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Format([DateFacture];"aaaa/mm") Entre (Année(Date())-1) & "/04" Et Année(Date()) & "/03"

Ci-joint la base...

[Invité]

Bonsoir,
c'est bien pour cela que j'avais proposé l'utilisation des iif et un where sur l'exercice concerné car un innert join ne fonctionne pas sur les mois non réciproques!

[maringot]

Bonjour user,

Merci pour ce correctif et excusez-moi de vous avoir contrarié mais je pensait avoir donné cette information avec le premier tableau du premier post.

Bien cordialement,
Maringot

[User]

Bonjour,

Bonjour user,

Merci pour ce correctif et excusez-moi de vous avoir contrarié mais je pensait avoir donné cette information avec le premier tableau du premier post.

Bien cordialement,
Maringot

Pas de soucis,

Mais je crois que dans ce genre de problème, on ne peut pas faire autrement que de s'appuyer sur des exemples.

Cdlt,

[maringot]

En réalisant les tests sur les différentes requêtes, je me suis aperçu que les cumuls ne sont pas correctes.
En effet, Le cumul d'avril, date du début de l'exercice, additionne le CA réalisé du 1er janvier au 30.04, donc le CA est erroné. Puis sur la ligne de mai, le cumul additionne le cumul d'avril et le CA mensuel de mai, voici ce que cela donne :

Pièce jointe 329303

Donc le souci vient du CA calculé en avril car il devrait apparaître les CA qui se trouvent dans le CA Mensuel...

[User]

J'ai mis à jour la base jointe ici:

J'avais oublié les formules du cumul :

Pour n :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CumulCA: (select Sum(MontantHT) From R_Facture T1 where Format(T1.DateFacture,"yyyy/mm") between year(Date()) & "/04" and Format(R_Facture.DateFacture,"yyyy/mm");)

Pour n-1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CumulCA: (select Sum(MontantHT) From R_Facture T1 where Format(T1.DateFacture,"yyyy/mm") between (year(Date())-1) & "/04" and Format(R_Facture.DateFacture,"yyyy/mm");)

Cdlt,