[ACCESS SQL] Combler les vides dans les enregistrements

**Arkham46** · 12/03/2009, 14h48

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 :

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

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 sql :

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

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 :

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

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 :

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

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!

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

réponse envoyée

**philben** · 14/03/2009, 09h59

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 :

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

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

**Arkham46** · 16/03/2009, 15h26

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 :

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

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

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 sql :

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

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!

**Jean-Philippe André** · 16/03/2009, 16h35

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

Code SQL :

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

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

**philben** · 16/03/2009, 20h56

bonjour,

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

Merci,

Philippe

**ilank** · 18/03/2009, 09h28

Bonjour,

perso j'avais ça :

Code sql :

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

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

**philben** · 19/03/2009, 01h28

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

Code :

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

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

**ilank** · 19/03/2009, 09h19

Waouh ! C'ti pas beau ça !!
Philben, deux mots Bra vo.

**Arkham46** · 22/03/2009, 22h46

sympa vos requêtes, merci de participer!

**Tofalu** · 18/06/2009, 22h20

Hello,

J'avais pas vu ce sujet

Une requête sans table supplémentaire et sans UNION

:

Code sql :

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

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

**sihamelm** · 19/10/2012, 11h38

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

**philben** · 20/10/2012, 23h11

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 :

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

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

Philippe

**dominoguismo** · 21/12/2012, 16h05

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 :

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

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

[ACCESS SQL] Combler les vides dans les enregistrements

Défis

Discussions similaires

Partager

Partager