Discussion :

[Invité]

Bonjour à tous
Je travaille sur une gestion de prêts.
Je dois faire des stats mensuelles du nombre de nouveaux cotisants sur, par exemple les douze derniers mois, ou plus précisément entre une date de début et une date de fin.
Jusque là pas de problème si tous les mois il y a de nouvelles entrées.
Par contre si pendant deux mois je n'ai pas de nouveaux achérents, comme il n'y a a pas d'enregistrement, la suite est interrompue. Or je voudrais que ces deux mois manquants apparaissent avec une valeur nulle.
Pour m'en sortir il faudrait que je puisse créer une requête temporaire m'affichant la liste de tous les mois compris entre ces deux dates. Et là je coince.
Merci pour votre aide à tous.

[philben]

bonjour,

il existe plusieurs méthodes :
- la création d'une table des mois puis lier la requete à cette table par un left join par exemple.
- S'il s'agit d'une RAC, on peut le faire sans table supplémentaire
- etc...

Pour vous répondre plus précisément, il faudrait :
- votre requete actuelle;
- la structure des tables de la requete;
- un court extrait des données par colonne des tables.

Philippe

[Invité]

Bonjour Philben
Merci pour ta réponse
Voici le code de ma requête du nombre de prêts mensuels.

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
 
      Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim strSQL As String
    Dim strWhere As String
    Dim strNomReq As String
    strNomReq = "Req_Stat_prêt1"
    Set MyDB = CurrentDb()
    strSQL = "SELECT DateSerial(Year(Date_de_sortie), Month(Date_de_sortie), 1) As Mois_sortie, Count (ID_mouvement) AS Total_prêts FROM Mouvements Where (Date_de_sortie Between Forms.Historique_prêts.Date_départ And Forms.Historique_prêts.Date_fin) GROUP BY DateSerial(Year(Date_de_sortie), Month(Date_de_sortie), 1)"
    strSQL = strSQL & strWhere
 
If TesteExistenceRequete("Req_Stat_prêt1") Then
 
    MyDB.QueryDefs.Delete strNomReq
 
    Set qdef = MyDB.CreateQueryDef("Req_Stat_prêt1", strSQL)
    Else
 
    Set qdef = MyDB.CreateQueryDef("Req_Stat_prêt1", strSQL)
 
End If

En voici le résultat
Mois_sortie Total_prêts
01/10/2007 65
01/11/2007 43
01/01/2008 5
01/02/2008 3
01/03/2008 7

On s'aperçoit bien qu'il manque le mois de décembre.
Il faudrait donc que je puisse avoir une rêquête qui me fasse une liste:
01/10/2007
01/11/2007
01/12/2007
01/01/2008
01/02/2008
01/03/2008

Après ce serait du gâteau.

[philben]

bonjour,

voici donc une solution à tester :
1) Création de tables
1.A) Table des Mois nommée <tMois>
Avec une colonne Clef Primaire <Mois> type Numérique - Octet
Ajouter 12 enregistrements contenant 1 à 12
1.B) Table des Années nommée <tAnnee>
Avec une colonne Clef Primaire <Annee> type numérique - Entier
Ajouter vos années par exemple de 2000 à 2020 ou plus

2) La requête
2.A) Principe
On sélectionne toutes les combinaisons Mois/Année qui correspondent à votre fourchette et on fait un left join sur votre table pour afficher les comptes pour chaque mois. Si un compte est null pour un mois, le mois est afficher quand même.
2.B) Code à ajouter

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
...
Dim dDebdate As Date, dFindate As Date
Dim sDebMois As String, sFinMois As String, sDebDate As String, sFinDate As String
...
dDebdate = Forms.Historique_prêts.Date_départ 
dFindate = Forms.Historique_prêts.Date_fin
 
sDebMois = Format$(DateSerial(Year(dDebdate), Month(dDebdate), 1), "\#m/d/yyyy\#")
sFinMois = Format$(DateSerial(Year(dFindate), Month(dFindate), 1), "\#m/d/yyyy\#")
sDebDate = Format$(dDebdate, "\#m/d/yyyy\#")
sFinDate = Format$(dFindate, "\#m/d/yyyy\#")

2.C) La Requête

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
strSQL = "SELECT R1.Mois_sortie, r2.Total_prêts " & _
       "FROM (SELECT DateSerial([annee],[mois],1) AS Mois_sortie " & _
       "FROM tMois, tAnnee WHERE DateSerial([annee],[mois],1) BETWEEN " & _
       sDebMois & " AND " & sFinMois & ") AS R1 " & _
       "LEFT JOIN (SELECT DateSerial(Year([Date_de_sortie]),Month([Date_de_sortie]),1) " & _
       "AS Mois_sortie, COUNT(Mouvements.ID_mouvement) As Total_prêts " & _
       "FROM Mouvements WHERE Mouvements.date_de_sortie BETWEEN " & _
       sDebDate & " AND " & sFinDate & " " & _
       "Group BY DateSerial(Year([Date_de_sortie]), Month([Date_de_sortie]),1)) " & _
       "AS R2 ON r1.Mois_sortie = r2.Mois_sortie " & _
       "Group BY R1.Mois_sortie, R2.Total_prêts"

Parfois les sous-requetes dans une clause FROM peuvent entrainer une erreur. Dans ce cas, il suffit de créer à part 2 requêtes qui seront liées dans une troisième.

Bonne soirée,

Philippe

[Invité]

Merci Philippe pour tes suggestions

Néanmoins j'ai continué ma petite idée concernant plutôt une table temporaire.
Je soumets mon code qui fonctionne. Il consiste à calculer le nombre de mois entre la date de départ et la date de fin, puis à remplir une table comprenant les mois théoriques entre ces deux dates avec une boucle correspondant à cet intervalle. Après il suffit de faire une requête combinant le tout et je me retrouve avec les mois n'ayant pas d'enregistrement avec une valeur 0

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
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim strSQL As String
    Dim strWhere As String
    Dim strNomReq As String
    strNomReq = "Req_Stat_prêt1"
    Set MyDB = CurrentDb()
 
    'Création requête regroupement du nombre de prêts par mois compris entre les dates de départ et dates de sortie du formulaire 'Mouvements'
    strSQL = "SELECT DateSerial(Year(Date_de_sortie), Month(Date_de_sortie), 1) As Mois_sortie, Count (ID_mouvement) AS Total_prêts " & _
    "FROM Mouvements " & _
    "Where (Date_de_sortie Between Forms.Historique_prêts.Date_départ And Forms.Historique_prêts.Date_fin)" & _
    "GROUP BY DateSerial(Year(Date_de_sortie), Month(Date_de_sortie), 1)"
    strSQL = strSQL & strWhere
 
If TesteExistenceRequete(strNomReq) Then
    MyDB.QueryDefs.Delete strNomReq
    Set qdef = MyDB.CreateQueryDef(strNomReq, strSQL)
    Else
    Set qdef = MyDB.CreateQueryDef(strNomReq, strSQL)
 
End If
 
'Création table des mois théoriques entre la date de départ et la date de fin
    Dim oNouvelleTable As DAO.TableDef
    Dim oRst As DAO.Recordset
    Dim oChamp As DAO.Field
    Dim strNomTable As String
    Dim MoisDep As Date  'mois de départ
    Dim Nb As Integer
    Dim i As Integer
 
Nb = DateDiff("m", Forms.Historique_prêts.Date_départ, Forms.Historique_prêts.Date_fin)
MoisDep = Form_Historique_prêts.Date_départ.Value
 
MyDB.TableDefs.Refresh
strNomTable = "Mois_Ref"
 
If VerifierExistenceTable(strNomTable:=strNomTable) = True Then
MyDB.TableDefs.Delete "Mois_Ref"
 'Crée la nouvelle table
    Set oNouvelleTable = MyDB.CreateTableDef(strNomTable)
'Crée le champ Mois
    Set oChamp = oNouvelleTable.CreateField("Mois", dbDate)
    oNouvelleTable.Fields.Append oChamp
    MyDB.TableDefs.Append oNouvelleTable
 
Else
    Set oNouvelleTable = MyDB.CreateTableDef(strNomTable)
    Set oChamp = oNouvelleTable.CreateField("Mois", dbDate)
    oNouvelleTable.Fields.Append oChamp
    MyDB.TableDefs.Append oNouvelleTable
End If
' crèe la boucle pour remplir la table
    For i = 1 To Nb + 1
Set oRst = MyDB.OpenRecordset(strNomTable, dbOpenTable)
 
         oRst.AddNew
        oRst.Fields("Mois").Value = DateAdd("m", i - 1, MoisDep)
         oRst.Update
    Next i
 
Set oChamp = Nothing
Set oNouvelleTable = Nothing
 
'Crée la requête liant la table et la requête et donnant une valeur 0 aux mois vides.
strNomReq = "Req_Stat_prêt2"
 
strSQL = "SELECT Mois_Ref.Mois, Req_Stat_prêt1.Total_prêts, IIf(Total_Prêts Not Like '',Total_prêts, 0) AS Prêts_mois " & _
"FROM Req_Stat_prêt1 " & _
"Right Join Mois_Ref On Req_Stat_prêt1.Mois_sortie = Mois_Ref.mois"
 
If TesteExistenceRequete(strNomReq) Then
    MyDB.QueryDefs.Delete strNomReq
    Set qdef = MyDB.CreateQueryDef(strNomReq, strSQL)
Else
    Set qdef = MyDB.CreateQueryDef(strNomReq, strSQL)
 
End If
 
MyDB.Close
Set oRst = Nothing
Set MyDB = Nothing
Set qdef = Nothing

Voici le résultat

Mois Prêts_mois
01/10/2007 65
01/11/2007 43
01/12/2007 0
01/01/2008 5
01/02/2008 3
01/03/2008 7