Créer un tableau de statistiques dynamique

Créer un tableau de statistiques dans un état Access à partir d'une requête analyse croisée.

Niveau requis : intermédiaire.

Commentez cet article : 11 commentaires Donner une note  l'article (5)

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

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 notre cas, nous avons choisi de présenter nos données statistiques sur un état tabulaire de 12 colonnes, une par mois, avec une période glissante. Cet état est alimenté par une requête analyse croisée et sa mise à jour ne nécessite que très peu de code VBA.

Nous allons tout d'abord donner un aperçu du résultat souhaité, ensuite, pour mieux comprendre la technique mise en oeuvre, nous décrirons les différents objets de notre base exemple, dans l'ordre de leur création.

  • Tables nécessaires
  • Requêtes
  • Formulaires
  • Etat

II. Rendu final du tableau

Il présente les montants réalisés pour chaque mois et pour chaque article sur une période glissante de 12 mois.

Aperçu du formulaire permettant de choisir la période et d'afficher le tableau de statistiques :

Image non disponible
Tableau des statistiques

Aperçu de l'état contenant le tableau :

Image non disponible
Tableau des statistiques

III. Tables nécessaires

Détaillons les tables permettant d'enregistrer les données.

III-A. Table T_Article

Elle permet d'enregistrer les informations concernant les articles.

Nom du champ Type de données Description
IdArticle Numéro-auto Identifiant de l'article
RefArticle Texte Référence de l'article
DesignationArticle Texte Désignation de l'article
PrixUnitaite Monétaire Prix unitaire de l'article
... ... ...

On garde uniquement les champs nécessaires pour notre base exemple.

III-B. Table T_Commande

Elle contient les informations des commandes clients.

Nom du champ Type de données Description
IdCommande Numéro-auto Identifiant de la commande
DateCommande Date/heure Date de la commande
DesignationCommande Texte Désignation de la commande
IdClient Entier long Identifiant du client concerné par la commande
... ... ...

On garde uniquement les champs importants.

III-C. Table T_DetailCommande

Elle permet d'enregistrer le détail de chaque commande.

Nom du champ Type de données Description
IdDetailCommande Numéro-auto Identifiant du détail de la commande
IdArticle Entier long Identifiant de l'article commandé
QteCommande Entier long Quantité d'articles commandés
PrixUnitaire Monétaire Prix unitaire de l'article
... ... ...

Là encore, on conserve uniquement les champs importants.

IV. Requêtes

Pour pouvoir afficher sur le tableau les commandes des 12 mois de la période choisie, il nous faut créer une requête analyse croisée qui alimentera notre état. Pour cela, il nous faut d'abord créer une requête sélection pour filtrer les commandes en fonction de la période.

IV-A. Requête R_DetailCommandes

Elle relie les table T_Commande et T_DetailCommande sur le champ IdCommande et sélectionne les commandes comprises dans la période choisie sur le formulaire F_TableauStats.

Image non disponible
Requête R_DetailCommande en mode création

Le champ calculé TotalLigne donne le montant total en fonction de la quantité d'articles commandée et de son prix unitaire :

 
Sélectionnez
TotalLigne: QteCommande*PrixUnitaire

La formule du champ IndMois donne l'indice du mois en fonction de la date de la commande et de la période choisie sur le formulaire F_TableauStats :

 
Sélectionnez
IndMois: DiffDate("m";SérieDate([Forms]![F_TableauStats]![CmbAnnee];[Forms]![F_TableauStats]![CmbMois];1);[DateCommande])+1

IV-B. Requête R_TableauStats

Cette requête analyse croisée affiche le tableau avec en en-têtes de ligne les articles, en en-têtes de colonne les mois de la période choisie, et à l'intersection la somme des montants des articles pour chacun des mois :

Image non disponible
Requête R_TableauStats en mode création

Le champ IndMois, placé en en-tête de colonne, donne l'indice du mois par rapport au 1er mois de la période choisie, il doit être compris entre 1 et 12. On va donc afficher uniquement les colonnes d'indices compris entre 1 et 12 :

Image non disponible
Propriétés du champ IdMois en mode création

Exemple d'affichage de la requête analyse croisée :

Image non disponible
Affichage de la requête R_TableauStats

V. Formulaires

La troisième étape consiste à créer les formulaires permettant de choisir une période et d'afficher le tableau de statistiques correspondant.

V-A. Sous-formulaire SF_TableauStats

Il est en mode continu et à comme source de données la requête analyse croisée R_TableauStats, vue précédemment.

Il comporte les éléments suivants :

  • Dans l'en-tête de formulaire, 12 étiquettes pour afficher les 12 mois de la période choisie ;
  • Dans le détail, la référence et la désignation de l'article ;
  • Toujours dans le détail, 12 zones de texte affichant les montants enregistrés pour chaque mois.
Image non disponible
Sous-formulaire en mode création

Ce sous-formulaire doit être intégré au formulaire principal F_TableauStats.

V-A-1. Etiquettes lblCol1..lblCol12

Positionnées dans l'en-tête du formulaire, elles permettent d'afficher les 12 mois de la période choisie.

Image non disponible
Etiquettes dans l'en-tête de page

V-A-2. Zones de texte txtRefArticle, txtDesigantionArticle

Placées dans le détail du formulaire, elles affichent la référence et la désignation de chaque article.

Image non disponible
Zones de texte txtRefArticle, txtDesigantionArticle

V-A-3. Zones de texte txtMois1..txtMois12

Disposées dans le détail du formulaire, elles affichent les montants des articles pour chaque mois de la période choisie.

Image non disponible
Zones de texte txtMois1..txtMois12

V-B. Formulaire F_TableauStats

Il contient, en plus du sous-formulaire, des listes déroulantes pour choisir 1er mois de la période, et des boutons de commandes pour se déplacer dans le temps.

Image non disponible
Sous-formulaire en mode création

V-B-1. Procédure MajTableauStats

Elle affiche, sur les en-têtes de colonne, les mois correspondant à la période, puis rafraîchit le sous-formulaire.

 
Sélectionnez
Private Sub MajTableauStats()
    ' Met à jour les en-têtes des 12 colonnes pour les 12 mois
    Dim i As Long, dt As Date
    
    dt = DateSerial(Me.cmbAnnee, Me.CmbMois, 1) ' date du 1er jour du mois sélectionné
    
    For i = 1 To 12 ' on parcourt les 12 étiquettes pour les 12 en-têtes de colonne
        ' on copie dans l'en-tête de la colonne d'indice i du sous-formulaire le mois correspondant à la date dt au format du type "0ct 19"
        Me.SF_TableauStats.Form("lblCol" & i).Caption = Format(dt, "mmm yy")
        dt = DateAdd("m", 1, dt) ' on passe au mois suivant
    Next i
    
    Me.SF_TableauStats.Requery ' on rafraîchit le sous*formulaire SF_TableauStats
End Sub

V-B-2. Bouton de commande CmdImprimer

Il permet d'afficher l'état correspondant en mode aperçu avant impression.

V-B-2-a. Procédure sur Clic

 
Sélectionnez
Private Sub CmdImprimer_Click()
On Error GoTo Err_CmdImprimer_Click

    Dim stDocName As String

    stDocName = "E_TableauStats"
    DoCmd.OpenReport stDocName, acPreview ' ouvre l'état E_TableauStats en mode aperçu avant impression

Exit_CmdImprimer_Click:
    Exit Sub

Err_CmdImprimer_Click:
    MsgBox Err.Description
    Resume Exit_CmdImprimer_Click
End Sub

V-B-3. Liste déroulante cmbMois

Elle permet de choisir le 1er mois et de mettre à jour le tableau en fonction de la période choisie.

V-B-3-a. Procédure sur AfterUpDate

 
Sélectionnez
Private Sub CmbMois_AfterUpdate()
    MajTableauStats ' actualise le tableau des statistiques
End Sub

V-B-4. Liste déroulante cmbAnnee

Elle permet de choisir l'année du 1er mois et de mettre à jour le tableau en fonction de la période choisie.

V-B-4-a. Procédure sur AfterUpDate

 
Sélectionnez
Private Sub cmbAnnee_AfterUpdate()
    MajTableauStats ' actualise le tableau des statistiques
End Sub

V-B-5. Bouton de commande CmdMoisPrecedent

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

V-B-5-a. Procédure sur Clic

 
Sélectionnez
Private Sub CmdMoisPrecedent_Click()
    
    If CmbMois.Value = 1 Then ' si la liste indique le mois d'indice 1 (janvier)
        CmbMois.Value = 12 ' on passe au mois de décembre
        cmbAnnee.Value = cmbAnnee.Value - 1 ' et à l'année précédente
    Else
        CmbMois.Value = CmbMois.Value - 1 ' on passe au mois précedent
    End If
    MajTableauStats ' actualise le tableau des statistiques
End Sub

Si la liste cmbMois affiche le mois de janvier, on recule d'une année et on passe au mois de décembre, sinon, on passe simplement au mois précédent.

V-B-6. Bouton de commande CmdMoisSuivant

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

V-B-6-a. Procédure sur Clic

 
Sélectionnez
Private Sub CmdMoisSuivant_Click()

    If CmbMois.Value = 12 Then ' si la liste indique le mois d'indice 12 (décembre)
        CmbMois.Value = 1 ' on passe au 1er mois (janvier)
        cmbAnnee.Value = cmbAnnee.Value + 1 ' et à l'année suivant
    Else
        CmbMois.Value = CmbMois.Value + 1 ' on passe au mois suivant
    End If
    MajTableauStats ' actualise le tableau des statistiques
End Sub

Si la liste cmbMois affiche le mois de décembre, on avance d'une année et on passe au mois de janvier, sinon, on passe simplement au mois suivant.

V-B-7. Bouton de commande CmdAnneePrecedente

Il permet de reculer d'une année sur le tableau.

V-B-7-a. Procédure sur Clic

 
Sélectionnez
Private Sub CmdAnneePrecedente_Click()
    
    Me.cmbAnnee = Me.cmbAnnee - 1 ' on passe à l'année précédente.
    Me.CmbMois = 1 ' on recale la période sur le 1er mois
    MajTableauStats ' actualise le tableau des statistiques
End Sub

V-B-8. Bouton de commande CmdAnneeSuivante

Il permet d'avancer d'une année sur le tableau.

V-B-8-a. Procédure sur Clic

 
Sélectionnez
Private Sub CmdAnneeSuivante_Click()

    Me.cmbAnnee = Me.cmbAnnee + 1 ' on passe à l'année suivante.
    Me.CmbMois = 1 ' on recale la période sur le 1er mois
    MajTableauStats ' actualise le tableau des statistiques
End Sub

V-B-9. Procédure sur Ouverture du formulaire

On actualise le tableau à l'ouverture du formulaire F_TableauStats pour mettre à jour les en-têtes de colonne.

 
Sélectionnez
Private Sub Form_Open(Cancel As Integer)
    MajTableauStats ' actualise le tableau des statistiques
End Sub

VI. Etat

VI-A. Etat E_TableauStats

Il est en mode tabulaire et à comme source de données la requête analyse croisée R_TableauStats, vue précédemment.

Il comporte les éléments suivants :

  • Dans l'en-tête de page, 12 étiquettes pour afficher les 12 mois de la période choisie ;
  • Dans le détail de l'état, la référence et la désignation de l'article ;
  • Toujours dans le détail, 12 zones de texte affichant les montants enregistrés pour chaque mois.
Image non disponible
Etat en mode création

VI-A-1. Etiquettes lblCol1..lblCol12

Positionnées dans l'en-tête de page, elles permettent d'afficher les 12 mois de la période choisie.

Image non disponible
Etiquettes dans l'en-tête de page

VI-A-2. Zones de texte txtRefArticle, txtDesigantionArticle

Placées dans le détail de l'état, elles affichent la référence et la désignation de chaque article.

Image non disponible
Zones de texte txtRefArticle, txtDesigantionArticle

VI-A-3. Zones de texte txtMois1..txtMois12

Disposées dans le détail de l'état, elles affichent les montants des articles pour chaque mois de la période choisie.

Image non disponible
Zones de texte txtMois1..txtMois12

VI-A-4. Procédure sur ouverture de l'état

Elle met à jour, sur l'événement ouverture de l'état, les en-têtes de colonne en fonction de la période choisie sur le formulaire.

 
Sélectionnez
Private Sub Report_Open(Cancel As Integer)
    Dim i As Long, dt As Date
    ' date du 1er jour de la période choisie sur le formulaire F_TableauStats
    dt = DateSerial(Forms!F_TableauStats.cmbAnnee, Forms!F_TableauStats.CmbMois, 1)
    For i = 1 To 12 ' on parcourt les 12 étiquettes pour les 12 en-têtes de colonne
        ' on copie dans l'en-tête de la colonne d'indice i du sous-formulaire le mois correspondant à la date dt au format du type "0ct 19"
        Me("lblCol" & i).Caption = Format(dt, "mmm yy")
        dt = DateAdd("m", 1, dt)
    Next i
End Sub

VII. Base de données à télécharger

La base exempletableau-stats.zip est au format accdb.

VIII. Remerciements

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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2010 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.