Un exemple intéressant de calculs de statistiques sur période mobile, ayant comme support la base les comptoirs, a déja été présenté par Jean-Damien Gayot.

Dans l'exemple et la technique que nous décrivons ici, les données sont représentées sur des graphiques et non pas sur des états ; d'autre part nous n'utilisons pas de tables temporaires afin d'alléger le code VBA : en effet l'objectif de cet article est en fait de présenter, sur un graphique, les statistiques des nuitées dans l'hôtellerie, durant une période mobile et modulable.

Par définition, le nombre de nuitées pour une chambre donnée est égal au produit de la durée du séjour par le nombre de personnes occupant cette chambre.

Pour ce faire, nous allons déterminer, au moyen d'une requête (R_Jour), l'ensemble des jours compris dans cette période, et pour chacun de ces jours, nous comptabiliserons le nombre de nuitées, afin de les totaliser ensuite mensuellement. Pour mieux faire comprendre la méthode, nous décrirons les différentes étapes (Tables, requêtes, formulaire, état) qui mènent à la création de ce graphique.

Pour illustrer ce choix, prenons le cas d'une réservation qui commence le 29 janvier 2010 et se termine le 7 février 2010 : la période étant à cheval sur 2 mois, le premier réflexe du programmeur est d'envisager, dans une requête basée sur la table des réservations, une fonction VBA qui dissocie la période du mois de janvier (du 29/01 au 31/01) de la période du mois de février (du 1/02 au 3/02) et comptabilise, pour chacune des périodes, le nombre de nuitées (cf. Annexe : la méthode avec fonction VBA).

Cependant, cette fonction VBA, ainsi que la requête qui l'utilise, font intervenir plusieurs conditions et sont assez complexes à décrire. Elles risquent donc de compliquer un peu les choses.

C'est pourquoi, dans notre exemple, nous utiliserons une autre méthode sans fonction VBA : si nous reprenons le cas d'une réservation qui commence le 29 janvier et se termine le 7 février, on va déterminer, pour cette réservation et en se servant de la requête R_Jour définie plus haut, tous les jours compris entre le 29 janvier et le 7 février et on affichera pour chaque jour le mois correspondant et le nombre de clients. Puis on comptabilisera dans une requête finale le nombre de personnes et donc les nuitées par mois.

Cette méthode peut également servir à générer des graphiques basés sur des statistiques journalières.

Voici le rendu du graphique qui présente les statistiques des nuitées pour la période choisie.

Dans ce paragraphe et dans la suite de l'article, nous utiliserons un exemple portant sur l'ensemble de l'année 2009, avec des réservations concernant les mois de juillet à novembre.

Nous allons tout d'abord définir les tables et les champs qui contiendront les données.

Pour dresser ces statistiques, nous aurons besoin de 4 tables :

- Les tables T_Jour, T_Mois, T_Annee vont nous servir à déterminer tous les jours et tous les mois de la période choisie, afin de compter par la suite, pour chacun des jours puis pour chacun des mois, le nombre de nuitées (cf. les requêtes R_Jour et R_Mois).

- La table T_ReservChambre contiendra les données statistiques à représenter sur le graphique (nombre de personnes, début et fin du séjour).

IV-A. La table "T_Jour"▲

IV-B. La table "T_Mois"▲

IV-C. La table "T_Annee"▲

IV-D. La table "T_ReservChambre"▲

Pour pouvoir afficher sur le graphique les nuitées en fonction des mois de la période choisie, il nous faut créer une requête R_MoisCpteNuitees qui constituera la source du graphique : Pour cela, nous allons d'abord créer les 2 sous-requêtes R_Jour et R_JourNuitees puis les requêtes R_Mois et R_MoisCpteNuitees.

V-A. La requête "R_Jour"▲

Cette requête, basée sur les tables T_Jour, T_Mois et T_Annee, réalise le produit cartésien entre ces 3 tables, de façon à afficher tous les jours de la période choisie. Elle a comme paramètres, pour le mois de début des statistiques, les listes déroulantes MoisD et AnneeD du formulaire F_Stats (Forms!F_Stats!MoisD, Forms!F_Stats!AnneeD) et, pour le mois de fin des statistiques, les listes déroulantes MoisF et AnneeF du formulaire F_Stats (Forms!F_Stats!MoisF, Forms!F_Stats!AnneeF).

Gros plan sur la condition qui sélectionne les jours compris dans la période choisie :

 

Sélectionnez

(Format([Mois] & "/" & [An],"yyyy/mm") Between Format([Forms]![F_Stats]![MoisD] & "/" & [Forms]![F_Stats]![AnneeD],"yyyy/mm") _ 
                                           And Format([Forms]![F_Stats]![MoisF] & "/" & [Forms]![F_Stats]![AnneeF],"yyyy/mm")))

Exemple d'affichage de la requête entre le mois de janvier de l'année 2009 et le mois de décembre 2009 compris.

V-B. La requête "R_JourNuitees"▲

Cette requête, basée sur la table T_ReservChambre et la requête précédente, affiche tous les jours réservés, ainsi que les mois concernés, et le nombre de clients par chambre réservée : le but est de déterminer, pour chaque réservation, tous les jours correspondants.

Exemple d'affichage de la requête entre le mois de janvier 2009 et le mois de décembre 2009 compris.

V-C. La requête "R_Mois"▲

Cette requête, basée sur les tables T_Mois et T_Annee, réalise le produit cartésien entre ces 2 tables, de façon à afficher tous les mois de la période choisie. Elle a comme paramètres, pour le mois de début des statistiques, les listes déroulantes MoisD et AnneeD du formulaire F_Stats (Forms!F_Stats!MoisD, Forms!F_Stats!AnneeD) et, pour le mois de fin des statistiques, les listes déroulantes MoisF et AnneeF du formulaire F_Stats (Forms!F_Stats!MoisF, Forms!F_Stats!AnneeF).

Exemple d'affichage de la requête entre le mois de janvier de l'année 2009 et le mois de décembre 2009 compris.

V-D. La requête "R_MoisCpteNuitees"▲

Cette requête, source des graphiques, est basée sur les requêtes R_Mois et R_JourNuitees. Elle affiche tous les mois de la période choisie, accompagnés de la somme des clients par mois : On effectue donc une jointure gauche entre les requêtes R_Mois et R_JourNuitees pour avoir tous les mois de la période choisie.

Exemple d'affichage de la requête entre le mois de janvier 2009 et le mois de décembre 2009 compris.

La troisième étape consiste à créer le formulaire qui va contenir le graphique.

VI-A. Le formulaire "F_Stats"▲

Il contient les listes déroulantes MoisD et AnneeD, pour permettre de choisir le mois du début de la période, et les listes MoisF et AnneeF, pour permettre de choisir le mois de fin de la période, et enfin l'objet graphique qui affiche les statistiques de la période choisie.

VI-A-1. La fonction PeriodeStats▲

 

Sélectionnez

Private Function PeriodeStats() As String
' Renvoie la période choisie sur le formulaire:
' Traduit les paramètres de début (Forms!F_Stats!MoisD, Forms!F_Stats!AnneeD) et de fin
' (Forms!F_Stats!MoisF, Forms!F_Stats!AnneeF) saisie sur le formulaire F_Stats en mois au format "mmm yyyy".

Dim m1 As Integer, m2 As Integer
Dim d1 As Date, d2 As Date

m1 = Forms!F_Stats!MoisD.Value
d1 = DateSerial(Forms!F_Stats!AnneeD.Value, m1, 1)

m2 = Forms!F_Stats!MoisF.Value
d2 = DateSerial(Forms!F_Stats!AnneeF.Value, m2 + 1, 0)

PeriodeStats = Format(d1, "mmmm yyyy") & " à " & Format(d2, "mmmm yyyy")

End Function

VI-A-2. Bouton de commande "CmdImprimer"▲

Il permet d'imprimer l'état contenant le graphique synchronisé avec le formulaire.

VI-A-2-a. Procédure événementielle sur Clic▲

 

Sélectionnez

Private Sub CmdImprimer_Click()

'Ouvre l'état "R_Stats" en mode aperçu avant impression.
DoCmd.OpenReport "R_Stats", acViewPreview

' Définit la période d'affiche des statistiques dans la légende de l'état :
' La fonction PeriodesStats renvoie la période choisie.
Reports!R_Stats.Caption = "Statistiques des nuitées de " & PeriodeStats

End Sub

VI-A-3. Liste déroulante "AnneeD"▲

Elle permet de choisir l'année de début des statistiques et d'afficher le graphique correspondant à la période choisie.

VI-A-3-a. Procédure événementielle sur AfterUpDate▲

 

Sélectionnez

Private Sub AnneeD_AfterUpdate()
' Actualise le graphique.
Graph.Requery

End Sub

VI-A-4. Liste déroulante "MoisD"▲

Elle permet de choisir le mois de début des statistiques et d'afficher le graphique correspondant à la période choisie.

VI-A-4-a. Procédure événementielle sur AfterUpDate▲

 

Sélectionnez

Private Sub MoisD_AfterUpdate()
' Actualise le graphique.
Graph.Requery

End Sub

VI-A-5. Liste déroulante "AnneeF"▲

Elle permet de choisir l'année de fin des statistiques et d'afficher le graphique correspondant à la période choisie.

VI-A-5-a. Procédure événementielle sur AfterUpDate▲

 

Sélectionnez

Private Sub AnneeF_AfterUpdate()
' Actualise le graphique.
Graph.Requery

End Sub

VI-A-6. Liste déroulante "MoisF"▲

Elle permet de choisir le mois de fin des statistiques et d'afficher le graphique correspondant à la période choisie.

VI-A-6-a. Procédure événementielle sur AfterUpDate▲

 

Sélectionnez

Private Sub MoisF_AfterUpdate()
' Actualise le graphique.
Graph.Requery

End Sub

VI-A-7. Bouton de commande "CmdPrecedent"▲

Il permet de reculer d'un mois sur le graphique.

VI-A-7-a. Procédure événementielle sur Clic▲

 

Sélectionnez

Private Sub CmdPrecedent_Click()

   If (Me!MoisD.Value > 1) Then ' Si le mois est supérieur à 1 (Janvier),
      Me.MoisD.Value = Me.MoisD.Value - 1  ' affiche le mois précédent.
      
   Else
      Me.MoisD.Value = 12  ' Sinon affiche le dernier mois
      Me!AnneeD.Value = Me!AnneeD.Value - 1 ' et l'année précédente.
      
   End If


   If (Me!MoisF.Value > 1) Then ' Si le mois est supérieur à 1 (Janvier),
      Me.MoisF.Value = Me.MoisF.Value - 1  ' affiche le mois précédent.
      
   Else
      Me.MoisF = 12 ' Sinon affiche le dernier mois
      Me.AnneeF = Me.AnneeF - 1 ' et l'année précédente.
      
   End If

' Affiche la période des statistiques dans le titre du formulaire.
Me.Titre.Caption = "Statistiques des nuitées de " & PeriodeStats 

Me.Graph.Requery ' Actualise le graphique.

End Sub

VI-A-8. Bouton de commande "CmdSuivant"▲

Il permet d'avancer d'un mois sur le graphique.

VI-A-8-a. Procédure événementielle sur Clic▲

 

Sélectionnez

Private Sub CmdSuivant_Click()

   If (Me!MoisD.Value < 12) Then ' Si le mois est inférieur à 12 (Décembre),
      Me.MoisD = Me.MoisD.Value + 1 ' affiche le mois suivant.
      
   Else
      Me.MoisD = 1 ' Sinon affiche le premier mois
      Me!AnneeD = Me!AnneeD + 1 ' et l'année suivante.
   End If

   If (Me!MoisF.Value < 12) Then ' Si le mois est inférieur à 12 (Décembre),
      Me.MoisF = Me.MoisF + 1 ' affiche le mois suivant.
      
   Else
      Me.MoisF = 1 ' Sinon affiche le premier mois
      Me.AnneeF = Me.AnneeF + 1 ' et l'année suivante.
      
   End If

' Affiche la période des statistiques dans le titre du formulaire.
Me.Titre.Caption = "Statistiques des nuitées de " & PeriodeStats

Me.Graph.Requery ' Actualise le graphique.

End Sub

VI-A-9. Création du graphique▲

Nous allons décrire les différentes étapes de la création du graphique avec l'assistant.

Choix de la commande Graphique dans le menu Insertion :

Choix de la requête R_MoisCpteNuitees sur la première fenêtre de l'assistant Graphique :

Choix des champs Mois et Cpte, de la requête source, pour le graphique :

Choix du type de graphique pour la présentation des données : dans notre cas nous opterons pour un histogramme.

Choix des champs correspondant respectivement à l'axe des abscisses et à l'axe des ordonnées :

Choix du titre pour le graphique des statistiques :

Affectation de la commande SQL (impliquant la requête R_MoisCpteNuitees) à la propriété Contenu de l'objet graphique :

Texte de la commande SQL de la propriété Contenu de l'objet graphique :

 

Sélectionnez

SELECT Format([Mois],"mm/yyyy"),Sum([Cpte]) AS [SommeDeCpte] 
FROM [R_MoisCpteNuitees] 
GROUP BY [Mois];

VI-A-10. Procédure événementielle sur Chargement▲

 

Sélectionnez

Private Sub Form_Load()
' Définit la période d'affichage des statistiques.

' Début de la période:
Me.MoisD.Value = 1 ' Mois de janvier.
Me.AnneeD.Value = Year(Date) ' Année en cours.

' Fin de la période:
Me.MoisF.Value = 12 ' Mois de décembre.
Me.AnneeF.Value = Year(Date) ' Année en cours.

' Affiche le titre du formulaire:
' La fonction PeriodesStats renvoie la période choisie.
Me.Titre.Caption = "Statistiques des nuitées de " & PeriodeStats

' Actualise le graphique.
Me.Graph.Requery 

End Sub

VII-A. L'état "R_Stats"▲

Il contient un objet graphique identique à celui du formulaire et il permet d'afficher un aperçu puis d'imprimer les statistiques de la période choisie.

On peut arriver au même résultat en utilisant une fonction VBA (NbNuitees) pour comptabiliser les nuitées.

Pour ce faire, il suffit de reprendre les tables T_Mois, T_Annee, T_ReservChambre et la requête R_Mois décrites précédemment, puis de remplacer la requête R_JourNuitees par la requête R_MoisNuitees et de modifier un peu la requête R_MoisCpteNuitees. Le formulaire F_Stats et l'état R_Stats restent eux inchangés.

VIII-A. La requête "R_MoisNuitees"▲

Cette requête, basée sur la table T_ReservChambre et la requête R_Mois, affiche tous les mois concernés, et le nombre de nuitées par mois : elle détermine pour chaque mois, la somme des nuitées par réservation calculées au moyen de la fonction VBA NbNuitees.

Exemple d'affichage de la requête entre le mois de janvier 2009 et le mois de décembre 2009 compris.

VIII-B. La requête "R_MoisCpteNuitees"▲

Cette requête, source des graphiques, est basée sur les requêtes R_Mois et R_MoisNuitees. Elle affiche tous les mois de la période choisie, accompagnés de la somme des clients par mois : pour déterminer tous les mois de la période choisie, on effectue une jointure gauche entre les requêtes R_Mois et R_MoisNuitees.

Exemple d'affichage de la requête entre le mois de janvier 2009 et le mois de décembre 2009 compris.

VIII-C. La fonction "NbNuitees"▲

Public Function NbNuitees(Mois As String, DateA As Date, DateF As Date, NbPers As Integer) As Integer
Dim DD As Date, DF As Date

DD = DateSerial(Year(Mois), Month(Mois), 1) '1er jour du mois.
DF = DateSerial(Year(Mois), Month(Mois) + 1, 0) ' Dernier jour du mois.

   If (DD <= DateA) And (DF >= DateF) Then ' Si la réservation est contenue entièrement dans le mois.
      NbNuitees = (DateF - DateA + 1) * NbPers ' Nombre de nuitées pour cette période.
   ElseIf (DD >= DateA) And (DF <= DateF) Then ' Si elle commence avant le mois et finit après.
      NbNuitees = (DF - DD + 1) * NbPers
   ElseIf (DD >= DateA) And (DD <= DateF) Then ' Si elle commence avant le mois et finit pendant.
      NbNuitees = (DateF - DD + 1) * NbPers
   ElseIf (DF >= DateA) And (DF <= DateF) Then ' Si elle commence pendant le mois et finit après.

      NbNuitees = (DF - DateA + 1) * NbPers
   Else
      NbNuitees = 0
   End If

End Function

La base exemple n°1bdStatsv1 est au format Access 2000.

La base exemple n°2bdStatsv2 est au format Access 2000 et utilise une fonction VBA pour compter les nuitées.

La base exemple n°3bdStatsv3. Il s'agit d'un exemple simplifié pour représenter, de la même façon sur un graphique, les quantités de produits par mois.

Je tiens à remercier Philippe JOCHMANS, Gayot, Jeannot45 et toute l'équipe Office pour m'avoir guidé dans la réalisation de cette article, ainsi que jacques_jean et Papy_77 pour leur relecture attentive.