[ACCESS SQL] Combler les vides dans les enregistrements

Version imprimable

Bonjour à tous,

Je vous propose de relever un petit défi rapide en réponse à un problème bien connu :
Comment combler les enregistrements vides dans une requête de regroupement par jour?

Les données :

une table TFactures :
Code:

1 2 3 4 5 6 7 8 9 10 nom datefacture volume PU haricot 01/02/2009 10 3 carotte 15/02/2009 20 2 carotte 20/02/2009 15 2,5 patates 27/01/2009 21 1,4 haricot 15/01/2009 4 3,3 choux 12/02/2009 3 1 choux 16/02/2009 1 1 patates 05/05/2009 2 1,2
Une requête RCA qui donne le chiffre d'affaire par jour pour un mois donné :
Code:

1 2 3 4 5 PARAMETERS année Short, mois Short; SELECT TFacture.datefacture AS dateCA, Sum([volume]*[PU]) AS ValeurCA FROM TFacture WHERE (((Year([datefacture]))=[année]) AND ((Month([datefacture]))=[mois])) GROUP BY TFacture.datefacture;
Résultat pour le mois de février 2009 :
Code:

1 2 3 4 5 6 7 dateCA ValeurCA 01/02/2009 30 12/02/2009 3 15/02/2009 40 16/02/2009 1 20/02/2009 37,5
Le défi :
Je souhaite obtenir en résultat :
Code:

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 30 DateCA ValeurCA 01/02/2009 30 02/02/2009 0 03/02/2009 0 04/02/2009 0 05/02/2009 0 06/02/2009 0 07/02/2009 0 08/02/2009 0 09/02/2009 0 10/02/2009 0 11/02/2009 0 12/02/2009 3 13/02/2009 0 14/02/2009 0 15/02/2009 40 16/02/2009 1 17/02/2009 0 18/02/2009 0 19/02/2009 0 20/02/2009 37,5 21/02/2009 0 22/02/2009 0 23/02/2009 0 24/02/2009 0 25/02/2009 0 26/02/2009 0 27/02/2009 0 28/02/2009 0
En résumé, on doit avoir une valeur égale à zéro pour les jours ne contenant pas d'enregistrements, afin de pouvoir afficher un graphique complet par exemple.

Vous pouvez prendre cette base de données (format access 2000) comme point de départ et modifier la requête RCA :
ftp://ftp-developpez.com/office/defi...quetejours.zip
(miroir)

Il n'est pas utile de modifier l'état et le formulaire pour obtenir le résultat.
On doit pouvoir exécuter directement la requête RCA pour obtenir le résultat voulu, celle-ci étant utilisée à plusieurs endroits elle doit être indépendante.

Vous pouvez ajouter des tables/requêtes/VBA, le principal est que la requête RCA fonctionne lorsqu'on l'appelle dans la fenêtre de base de données, et que l'état et le graphique fonctionne également sans modification.

Notez qu'il est possible pour les plus motivés de relever le défi uniquement en modifiant la requête RCA, sans ajouter de table ou de code VBA.

Si un peu de SQL vous tente, on attend vos requêtes!
Postez votre solution à la suite de ce message.

A vos méninges! ;)

12/03/2009, 23h32
Jean-Philippe André

réponse envoyée :aie:

Une solution Table

bonjour,

une solution via table.

Créer une table nommée <tJours> ayant qu'une seule colonne nommée <Jour> de type <Numérique> et de taille du champ <Octet>
Créer 31 enregistrements avec valeur croissante de 1 à 31
Modifier la requête RCA :
Code:

1 2 3 4 5 6 PARAMETERS année Short, mois Short; SELECT [Jours] AS DateCA, NZ(Sum([volume]*[PU]),0) AS ValeurCA FROM (SELECT DateSerial([Année],[Mois],[Jour]) AS Jours from tJours) AS TJ LEFT JOIN tfacture AS TF ON TJ.Jours = TF.datefacture WHERE (((Year([Jours]))=[année]) AND ((Month([Jours]))=[mois])) GROUP BY Jours;
Philippe

Citation:

Envoyé par philben

bonjour,

une solution via table.

Créer une table nommée <tJours> ayant qu'une seule colonne nommée <Jour> de type <Numérique> et de taille du champ <Octet>
Créer 31 enregistrements avec valeur croissante de 1 à 31
Modifier la requête RCA :

Code:

1
2
3
4
5
6
 
PARAMETERS année Short, mois Short;
SELECT [Jours] AS DateCA, NZ(Sum([volume]*[PU]),0) AS ValeurCA
FROM (SELECT DateSerial([Année],[Mois],[Jour]) AS Jours from tJours) AS TJ LEFT JOIN tfacture AS TF ON TJ.Jours = TF.datefacture
WHERE (((Year([Jours]))=[année]) AND ((Month([Jours]))=[mois]))
GROUP BY Jours;

Philippe

philben dégaine toujours aussi vite :king:

Je pense que la solution de passer par une table est la meilleure.
Pour le fun on peut générer la table des jours avec des Union.
jpcheck l'avait fait (tu peux mettre ta requête à la suite si tu veux ;))

Pour ma part j'avais écris ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
PARAMETERS Année Short, Mois Short;
SELECT RJours.DateJour AS DateCA, nz(Sum([volume]*[PU]),0) AS ValeurCA
FROM (SELECT DateSerial([Année],[Mois],Tjours.jour) AS DateJour FROM
 [select top 1 1 as jour  from TFacture union select top 1 2 from TFacture union  select top 1 3 from TFacture
 union select top 1 4 from TFacture union  select top 1 5 from TFacture union select top 1 6 from TFacture
 union select top 1 7 from TFacture union select top 1 8 from TFacture union  select top 1 9 from TFacture
 union select top 1 10 from TFacture union select top 1 11 from TFacture union select top 1 12 from TFacture
 union  select top 1 13 from TFacture union select top 1 14 from TFacture union select top 1 15 from TFacture
 union  select top 1 16 from TFacture union select top 1 17 from TFacture union select top 1 18 from TFacture
 union select top 1 19 from TFacture  union  select top 1 20 from TFacture union select top 1 21 from TFacture
 union select top 1 22 from TFacture union select top 1 23 from TFacture union  select top 1 24 from TFacture
 union select top 1 25 from TFacture union select top 1 26 from TFacture union  select top 1 27 from TFacture
 union select top 1 28 from TFacture union select top 1 29 from TFacture union select top 1 30 from TFacture union  select top 1 31 from TFacture]. as Tjours
 WHERE Month(DateSerial([Année],[Mois],TJours.jour))=[Mois]) AS RJours LEFT JOIN TFacture ON RJours.DateJour = TFacture.datefacture
GROUP BY RJours.DateJour
ORDER BY RJours.DateJour;

Je n'avais pas mis de test sur l'année vu que je m'étais dis que le DateSerial ne doit pas donner lieu à un changement d'année, décembre ayant 31 jours.

Ca marche bien mais l'imbrication des sous-requêtes ne plaît pas beaucoup lors du passage de QBE à SQL.
Si je devais l'utiliser je passerais par une petite table des jours.

Bravo Philippe!

de mon côté, juste une requête aussi :

Code:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
PARAMETERS année Short, mois Short;
 
SELECT TFacture.datefacture AS dateCA, Sum([volume]*[PU]) AS ValeurCA
FROM TFacture
WHERE (((Year([datefacture]))=[année]) AND ((Month([datefacture]))=[mois]))
GROUP BY TFacture.datefacture
 
UNION
SELECT dateserial([année],[mois],1) AS dateCA, 0 AS valeurCa  FROM TFacture  WHERE dateserial([année],[mois],1) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],2) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],2) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],3) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],3) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],4) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],4) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],5) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],5) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],6) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],6) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],7) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],7) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],8) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],8) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],9) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],9) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],10) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],10) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],11) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],11) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],12) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],12) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],13) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],13) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION 
SELECT dateserial([année],[mois],14) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],14) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],15) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],15) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION 
SELECT dateserial([année],[mois],16) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],16) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],17) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],17) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],18) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],18) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],19) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],19) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],20) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],20) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],21) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],21) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],22) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],22) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],23) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],23) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],24) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],24) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],25) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],25) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],26) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],26) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],27) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],27) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],28) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],28) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois])
UNION
SELECT dateserial([année],[mois],29) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],29) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],29))=[année] AND Month(dateserial([année],[mois],29))=[mois]
UNION
SELECT dateserial([année],[mois],30) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],30) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],30))=[année] AND Month(dateserial([année],[mois],30))=[mois]
UNION
SELECT dateserial([année],[mois],31) AS dateCA, 0 AS valeurCa  FROM TFacture WHERE dateserial([année],[mois],31) NOT IN (SELECT datefacture FROM Tfacture WHERE year(datefacture)=[année] AND month(datefacture)=[mois]) AND Year(dateserial([année],[mois],31))=[année] AND Month(dateserial([année],[mois],31))=[mois];

16/03/2009, 20h56
philben

bonjour,

je me demandais comment vous faisiez pour générer des dates qui n'existaient pas... :king:

Merci,

Philippe

Bonjour,

perso j'avais ça :

Code:

1
2
3
4
5
6
7
8
 
PARAMETERS Année short, Mois short;
TRANSFORM Nz(Sum(PU*Volume),0)
SELECT Format(mois & "/" & année,"mm/yyyy")
 FROM TFacture
 WHERE Month(DateFacture)=Mois AND Year(DateFacture)=Année
 GROUP BY Format(mois & "/" & année,"mm/yyyy")
 PIVOT Day(DateFacture) In (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,30,31);

Pour le fun, la requête tout en un :

http://pbserv.free.fr/images/RqGraphe.gif

Code:

1
2
3
4
5
6
 
PARAMETERS année Short, mois Short;
SELECT Jours AS DateCA, IIf(NZ(Sum([volume]*[PU]),0)>0,String(Sum([volume]*[PU])*30/(SELECT TOP 1 Sum([volume]*[PU]) FROM TFacture WHERE Year([datefacture])=[année] AND Month([datefacture])=[mois]  GROUP BY datefacture ORDER BY Sum([volume]*[PU]) DESC),CHRW(9608)) & Format(Sum([volume]*[PU])," #.0 €"),ChrW(9612) & " 0 €") AS Graphe
FROM (SELECT DateSerial([Année],[Mois],[Jour]) AS Jours from tJours) AS TJ LEFT JOIN tfacture AS TF ON TJ.Jours = TF.datefacture
WHERE Year([Jours])=[année] AND Month([Jours])=[mois]
GROUP BY Jours;

D'après une idée ici.

Philippe

19/03/2009, 09h19
ilank

Waouh ! C'ti pas beau ça !!
Philben, deux mots Bra vo.
22/03/2009, 22h46
Arkham46

sympa vos requêtes, merci de participer!

:king:

Hello,

J'avais pas vu ce sujet

Une requête sans table supplémentaire et sans UNION :mouarf::
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT LADATE, NZ(SUM(PU*VOLUME),0) FROM ( SELECT TOP 31 DATESERIAL(ANNEE,MOIS,ID1 & ID2) AS LADATE FROM (SELECT TOP 4 (SELECT Count(ID) FROM MSysObjects TA WHERE TA.ID<TA1.ID) AS ID1 FROM MSysObjects TA1 ORDER BY ID) T1 , (SELECT TOP 10 (SELECT Count(ID) FROM MSysObjects TA WHERE TA.ID<TA1.ID) AS ID2 FROM MSysObjects TA1 ORDER BY ID) T2 WHERE Month(DATESERIAL(ANNEE,MOIS,ID1& ID2))=MOIS ORDER BY ID1 & ID2 ) TJOURS LEFT JOIN TFacture ON TFacture.datefacture=TJOURS.LADATE GROUP BY LADATE
Il doit y avoir plus simple pour la comnbinaison des jours mais j'ai pas voulu prendre le risque de me retrouver dans un cas de figure où ça foire et me faire jeter d'ici :aie:

19/10/2012, 11h38
sihamelm

Bonjour à tous,
J'ai en fait besoin de, presque, la meme chose sauf que je veux avoir un formulaire qui m'affiche tous les jours de l'année et non pas seulement un mois
Comment faire?
MErci
20/10/2012, 23h11
philben
bonjour,

une solution consiste à créer :
une table tJour avec une colonne Jour numerique entier - clef primaire qui contiendra 31 lignes avec 1,2,3,...31
une table tmois avec une colonne mois numerique entier - clef primaire qui contiendra 12 lignes avec 1,2,3,...12

et la requête pour générer toutes les dates de l'année 2012 :
Code:

1 2 3 4 5 SELECT DISTINCT Dateserial(2012, [mois], [jour]) AS Dates FROM tmois, tjour ORDER BY Dateserial(2012, [mois], [jour])
Philippe
21/12/2012, 16h05
dominoguismo
Citation:
Envoyé par philben

bonjour,

une solution consiste à créer :
une table tJour avec une colonne Jour numerique entier - clef primaire qui contiendra 31 lignes avec 1,2,3,...31
une table tmois avec une colonne mois numerique entier - clef primaire qui contiendra 12 lignes avec 1,2,3,...12

et la requête pour générer toutes les dates de l'année 2012 :

Code:

1 2 3 4 5 SELECT DISTINCT Dateserial(2012, [mois], [jour]) AS Dates FROM tmois, tjour ORDER BY Dateserial(2012, [mois], [jour])

Philippe
j aimerai utilisé ton code mais j arrive pas :(