Comment maîtriser la génération de graphiques pour vos applications Access

Je tiens à remercier toutes celles et tous ceux qui ont participé à la relecture de ce document en y incluant leurs remarques.

Pour tous renseignements complémentaires, veuillez me contacter directement (Argyronet) par MP.

Le formulaire que vous allez réaliser se présente ainsi.
Il se compose d'un graphique, de deux cadres d'options et d'une liste combo répertoriant les catégories.
Ces cadres permettent de faire évoluer l'affichage du graphique selon un choix défini.
Le bouton fermer se contente de fermer le formulaire : je vous laisse la liberté d'enrichir le code qui l'accompagne.

Avant de concevoir le formulaire devant contenir le graphique, il vous faut d'abord concevoir la requête qui en sera la source.
Pour ce faire, sélectionnez l'onglet du gestionnaire de requêtes et cliquez sur le bouton Nouveau de manière à créer une nouvelle requête vierge

Pour mettre en œuvre cette requête, il vous faut sélectionner les tables suivantes :

Liste des tables pour réalisation de la requête

• Commandes ;
• Détail des commandes ;
• Produits ;
• Catégories.

Depuis le bouton Ajouter une table ou depuis le menu Requête/Ajouter une table, sélectionnez les tables comme ci-après puis cliquez sur Ajouter.

Vous les disposez de manière à ce que les champs à utiliser soient accessibles.

La requête que nous allons créer est une requête Analyse croisée.
Pour créer une telle requête, vous devez choisir dans le menu requête la rubrique du même nom :

Dans la grille, les En-têtes de ligne, l'En-tête de Colonne et la colonne Valeur sont définies dans un premier temps avec les champs suivants :

Liste des champs pour l'analyse croisée

• En-têtes de ligne : Nom de catégorie ;
• En-têtes de ligne : Date commande ;
• En-tête de colonne : Date commande ;
• Valeur : Prix unitaire.

Pour concevoir cette analyse croisée, il est nécessaire de mettre en place des formules :

Liste des formules pour l'analyse croisée

• En-têtes de ligne : Nom de catégorie ;
• En-têtes de ligne : Année : Year([Date commande]) ;
• En-tête de colonne : Trimestres : "Trim" & DatePart("q";[Date commande];1)
• Valeur : Montant : CCurrency([Détails commandes].[Prix unitaire]*[Quantité]*(1-[Remise (%)])/100)*100

=> Le nom de catégorie reste tel quel.
=> Pour obtenir l'année de commande, la formule utilise la fonction Year() pour extraire l'année de la date de commande.
=> Pour décomposer les périodes de date de commande en trimestres, la formule utilise la fonction DatePart().
=> Pour calculer le montant de la commande, on multiplie le Prix Unitaire par la Quantité où l'on déduit la Remise.

Vous obtenez alors la grille suivante :

Vous pouvez l'écrire sous forme SQL directement si vous êtes à l'aise avec cette méthode.

Requête Analyse sous forme SQL

TRANSFORM Sum(CCur([Détails commandes].[Prix unitaire]*[Quantité]*(1-[Remise (%)])/100)*100) AS Montant
SELECT Catégories.[Nom de catégorie], Year([Date commande]) AS Année
FROM (Catégories 
    INNER JOIN Produits 
        ON Catégories.[Code catégorie] = Produits.[Code catégorie]) 
    INNER JOIN (Commandes 
    INNER JOIN [Détails commandes] 
        ON Commandes.[N° commande] = [Détails commandes].[N° commande]) 
        ON Produits.[Réf produit] = [Détails commandes].[Réf produit]
GROUP BY Catégories.[Nom de catégorie], Year([Date commande])
PIVOT "Trim " & DatePart("q",[Date commande],1) 
IN ("Trim 1","Trim 2","Trim 3","Trim 4");

Une fois votre requête établie, il vous faut la sauvegarder.
Par le biais du menu fichier ou en cliquant sur l'icône idoine, enregistrez la requête sous un nom explicite par exemple : reqPvtCommandesTrimestriellesParCatégorie.

Cette partie est terminée. Vous pouvez sélectionner l'onglet de gestion des formulaires afin de créer un formulaire vierge.

Pour concevoir le formulaire devant recevoir le graphique, choisissez Nouveau depuis la fenêtre Base de données sans sélectionner de source.
L'image ci-après apparaît où vous sélectionnez alors Mode création puis, vous appuyez sur OK.

Un formulaire vierge vient se substituer à la fenêtre précédente en vue de recevoir les contrôles que vous souhaitez y poser.

Avant de commencer, agrandissez un peu la zone de travail puis…

sélectionnez, dans le menu Insertion, la rubrique Graphique…

Pointez ensuite le curseur en haut et à gauche et dessinez un rectangle centré d'environ 70 % de la surface du formulaire.

Une fois que vous relâchez le bouton gauche de la souris, une boîte de dialogue apparaît vous invitant à sélectionner une source depuis une table ou une requête. Vous venez de charger l'Assistant Graphique qui est dédié à vous aider dans la conception d'un graphique comme dans Excel.
Malheureusement, ce dernier n'est pas aussi souple que pour le tableur et il vous faut dans un premier temps ne pas chercher à sophistiquer le graphique, mais plutôt d'opter pour le plus simple : c'est ce que vous allez mettre en œuvre.

=> Cochez alors l'option Requêtes puis sélectionnez la requête que vous avez créée précédemment ; enfin, cliquez sur Suivant.

Ici, les champs de la requête apparaissent dans la liste de gauche.

Pour cet exemple de conception du graphique, vous allez dans un second temps sélectionner les champs suivants :

• Nom de catégorie ;
• Trim 1 ;
• Trim 2 ;
• Trim 3 ;
• Trim 4.

Ce choix va vous permettre de visualiser graphiquement les catégories de produits vendues sur la période complète et réparties par trimestre.
En effet, il semble délicat et confus d'inclure l'année ici, car la représentation serait surchargée.
Dans l'idéal, il aurait mieux valu concevoir une requête par cas, mais bon, considérant que l'on peut choisir les champs, autant ne pas faire de zèle pour cet aspect de conception qui présente en général un point moins majeur que le reste de l'application finie.

Cliquez sur Suivant

Ici, laissez les options par défaut : en effet, l'histogramme est plus parlant pour notre cas.
Un peu plus bas, vous verrez comment concevoir un camembert avec des répartitions par pourcentage.

Cliquez sur Suivant

Vous obtenez donc ci-dessus, un préaperçu du graphique avec ce que l'Assistant Graphique a estimé de mieux pour la disposition des axes X Y.

Afin de pouvoir illustrer la répartition des Catégories de produits vendues par trimestre avec une couleur représentative associée à une légende, il vous faut glisser les rubriques manquantes Trim 2 à Trim 4 dans la zone Données actuellement renseignée qu'avec le 1^er trimestre, comme ci-dessous :

Bien entendu, vous pourriez la laisser telle quelle, mais cela impacterait sur le résultat attendu qui ne concernerait ici qu'un trimestre.

La dernière étape consiste à renseigner le titre du graphique.
Du fait que le formulaire possédera déjà un titre, il n'est pas indispensable de renseigner cette zone qu'il est toujours possible de modifier en mode création.

Cliquez sur Terminer

Comme vous pouvez le constater, le graphique obtenu en mode création est absolument incohérent par rapport aux données de la requête.
Et pour cause, Microsoft Access et Microsoft Graph ne sont pas très bons amis par conception, car le résultat final de votre graphique n'est visible qu'en mode formulaire, ce qui, je vous l'accorde est loin d'être pratique.

Je vais donc vous apprendre à corriger cela.

Le graphique fini en mode création

Il en est de même pour le graphique obtenu en mode formulaire : non seulement il est affreux, mais en plus, il est totalement illisible.

Le graphique fini en mode formulaire

Passez en mode création si ce n'est pas déjà fait et cliquez deux fois sur le graphique afin d'obtenir un contour hachuré autour du graphique lui-même qui fait apparaître par là même, la feuille de données qui l'accompagne.

Là, vous constatez que malgré la sélection de la source de données qui est celle de la requête, les données ne correspondent pas à ce qui est attendu.

Il y a alors nécessité pour travailler votre graphique, de supprimer ces données au profit de celles de la requête.

En effet, pour mettre en forme le graphique, vous êtes en quelque sorte contraint de passer par cette étape qui peut paraître être assimilée à du bricolage ; néanmoins, elle s'effectue une bonne fois pour toutes même si les données évoluent dans le temps.

Remplacement des données existantes

Vous sélectionnez alors les données de la feuille et appuyez sur la touche Suppr ou bien effectuez cette opération par le Menu contextuel :

Ouvrez alors la requête en mode Feuille de données et copiez le contenu de la plage représentant l'ensemble des données.

Repassez alors en Mode création du graphique où vous affichez de nouveau la feuille de données pour y coller le contenu du presse-papier à partir de la cellule 0,0 comme le montre l'illustration ci-dessous :

Vous obtenez alors un affichage instantané du graphique (toujours aussi illisible), mais qui correspond cette fois aux données de votre requête.

S

Profitez de cette situation pour arranger votre plan de travail, à savoir, ajuster la feuille de données en Lignes et Colonnes et par la même occasion, changer les intitulés d'en-tête de colonne en supprimant le mot SommeDe.
Pour ce faire, vous ne pouvez pas (sinon temporairement, car ce n'est pas mémorisé) procéder comme dans Excel, à savoir sélectionner la cellule et y saisir le mot Trim 1 et ainsi de suite jusqu'à Trim 4.

II-E-1. Modification des intitulés d'en-tête de colonnes▲

Pour modifier les en-têtes, cliquez une seule fois sur le graphique de manière à le délimiter par 8 points d'ancrage comme vous procéderiez pour n'importe quel contrôle de formulaire puis cliquez sur le bouton Propriétés

La boîte de dialogue des propriétés s'affiche et là, vous pointez votre souris sur la zone Contenu.


La requête SQL est accessible ici donc modifiable : cliquez sur cette rubrique de manière à la mettre en surbrillance puis copiez cette dernière via les touches Ctrl+C ou bien avec le clic droit de la souris pour choisir Copier.

Requête du graphique sous forme SQL

 

Sélectionnez

SELECT [Nom de catégorie],Sum([Trim 1]) AS [SommeDeTrim 1],
                          Sum([Trim 2]) AS [SommeDeTrim 2],
                          Sum([Trim 3]) AS [SommeDeTrim 3],
                          Sum([Trim 4]) AS [SommeDeTrim 4]
FROM [reqPvtCommandesTrimestriellesParCatégorie]
GROUP BY [Nom de catégorie];

Ouvrez un éditeur de texte quelconque, par exemple, Notepad (Démarrer + Exécuter + Notepad) puis coller-y directement le contenu.

De là, remplacez manuellement ou utilisez le menu Édition/Remplacer pour remplacer l'occurrence « SommeDeTrim » en y incluant l'espace par l'occurrence vide "".
Ensuite, derrière chacun de ces alias, mettez un « er » derrière le 1 et un « e » derrière les trois autres chiffres suivi du mot « Trim ».

Vous devez alors obtenir :

Selectionnez alors le bloc de texte dans son entier et copiez-le de nouveau.
Retournez sous Microsoft Access et dans la rubrique contenu qui est toujours en surbrillance, appuyez sur Suppr du clavier puis sur Ctrl+V pour coller la chaîne SQL ainsi modifiée.

Bien entendu, vous auriez très bien pu modifier directement les alias dans la rubrique elle-même.

L'usage de Notepad permet de faciliter le travail et d'éviter les omissions ou confusions génératrices d'erreurs.

Finalement, lorsque vous affichez de nouveau le graphique en mode formulaire puis en mode création, la feuille de données apparaît alors comme ceci :

ATTENTION !!!

Vous n'obtiendrez la mise à jour des en-têtes des colonnes que lorsque vous aurez tout de suite après avoir modifié la requête SQL, affiché le graphique en mode Formulaire ; c'est malheureusement la façon de procéder.

II-E-2. Comment utiliser la feuille de données ?▲

La feuille de données de l'Assistant graphique n'est pas aussi souple qu'une feuille de calcul Excel.
Vous disposez de façon similaire des fonctionnalités liées aux propriétés des lignes, des colonnes et des cellules.
Un petit degré d'apprentissage est toutefois nécessaire afin d'être à l'aise, car il est vrai qu'on y perd vite son latin.

Affichage/Masquage de lignes ou de colonnes

Pour afficher ou masquer une ligne ou une colonne, il vous suffit d'effectuer un double-clic sur l'en-tête de celle-ci.
L'intitulé devenant grisé vous indique que celle-ci sera masquée.

Ci-dessous, seuls les Produits laitiers et les Produits secs pour le 1^er trimestre seront représentés sur le graphique.

Pour réafficher la ligne ou la colonne masquée, il suffit de procéder à un nouveau double-clic comme précédemment.

Les cellules comme les lignes ou les colonnes possèdent des propriétés limitées : elles sont contextuelles et ne concernent que la mise en forme du format, de la police ou encore leurs dimensions.
L'intérêt est vraiment minime, car la feuille de données n'est pas affichable dans le formulaire telle que vous la voyez ici.

II-E-3. Comment modifier l'aspect du graphique▲

Maintenant, le graphique lui-même a besoin d'être mis en forme.
Pour ce faire, commencez par ajuster la partie hachurée délimitant le graphique jusqu'au bord bas droit de la zone du contrôle graphique.

Aussitôt, l'aspect est un peu plus lisible, mais toujours pas présentable.

Là, procédez par zone pour changer la mise en forme de chacune d'entre elles :

• cliquez d'abord deux fois sur l'axe des Y (Zone de nombres) et changez la Taille de la police à 8 et le Format en Nombre à 2 décimales ;
• cliquez ensuite deux fois sur l'axe des X (Zone de catégories) et changez la Taille de la police à 8 ;
• cliquez pour continuer sur le Titre et passez sa taille à 14 points pour le terme « Commandes trimestrielles » et à l'aide de la combinaison Maj+Entrée au niveau du terme « par catégorie », vous appliquez une taille de 10 points non Gras ;
• cliquez sur la Légende et passez sa taille à 8 points ; enlevez le Gras et étirez la zone horizontalement de manière à la loger tout en bas à droite du graphique ;
• Cliquez deux fois sur le Quadrillage principal de l'axe des ordonnées et dans la liste des Couleurs, choisissez un gris très clair puis dans la liste des Styles, choisissez le ^3e pointillé ;
• cliquez une fois sur chacune des barres colorées et appliquez-leur une couleur « plus pastel » que celle apposée avec pourquoi pas, un motif dégradé bicolore en cliquant sur le bouton Motifs et textures.

Vous devez alors obtenir :

Il est probable que vous n'ayez pas assez d'espace de travail pour tout ajuster, aussi, n'hésitez pas à cliquer sur la partie gris foncé du formulaire Microsoft Access de manière à réagrandir de nouveau la taille du contrôle.

Si c'est le cas, n'omettez pas de réajuster la zone hachurée au contenu interne comme nous l'avons vu plus haut.

II-E-4. Éviter que l'utilisateur modifie le graphique▲

Le code VBA pour mettre à jour le graphique en fonction des options sélectionnées est relativement simple.
Il est supervisé par un jeu de SELECT CASE imbriqués qui permettent de savoir ce qui est coché.

La liste des événements associés aux différents contrôles est la suivante :

Liste des événements

• fraCategories_AfterUpdate : événement Après Mise à Jour du cadre des catégories ;
• cmbCategories_AfterUpdate : événement Après Mise à Jour de la liste déroulante des catégories ;
• fraQuartet_AfterUpdate : événement Après Mise à Jour du cadre des trimestres ;
• cmdClose_Click : événement Clic du bouton Fermer.

Option Compare Database
Option Explicit


Private Sub cmbCategories_AfterUpdate()
Dim strSQL As String
Dim strCategory As String
  'Initialisation de la variable au contenu de la liste
  strCategory = Me!cmbCategories.Text
  'Initialisation de la chaîne SQL
  strSQL = "SELECT [Nom de catégorie], Sum([Trim 1]) AS [1er Trim]," & _
  "Sum([Trim 2]) AS [2e Trim],Sum([Trim 3]) AS [3e Trim],Sum([Trim 4]) AS [4e Trim]"
  strSQL = strSQL & vbCrLf & "FROM reqPvtCommandesTrimestriellesParCatégorie"
  strSQL = strSQL & vbCrLf & "GROUP BY [Nom de catégorie]"
  'Initialisation de la condition WHERE avec la catégorie
  strSQL = strSQL & vbCrLf & "HAVING ((([Nom de catégorie])=" & Chr(34) & _
  strCategory & Chr(34) & "));"
  'Affectation du controle Frame
  Me!fraCategories = 2
  'Affectation de la nouvelle source et mise à jour de graphique
  ChartSalesByQuartet.RowSource = strSQL
  ChartSalesByQuartet.Requery
End Sub

Private Sub cmdClose_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub fraCategories_AfterUpdate()
Dim strSQL As String
  
  Select Case Me!fraCategories
    Case 1 'Toutes
    'Désactivation du ComboBox et vidage
      cmbCategories.Enabled = False
      Select Case fraQuartet
        Case 1 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 1]) AS [1er Trim]"
        Case 2 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 2]) AS [2e Trim]"
        Case 3 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 3]) AS [3e Trim]"
        Case 4 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 4]) AS [4e Trim]"
        Case 5 'Tous
          strSQL = "SELECT [Nom de catégorie],Sum([Trim 1]) AS [1er Trim], " & _
          "Sum([Trim 2]) AS [2e Trim],Sum([Trim 3]) AS [3e Trim],Sum([Trim 4]) AS [4e Trim]"
      End Select
      strSQL = strSQL & vbCrLf & "FROM reqPvtCommandesTrimestriellesParCatégorie"
      strSQL = strSQL & vbCrLf & "GROUP BY reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie];"
      ChartSalesByQuartet.RowSource = strSQL
      ChartSalesByQuartet.Requery
    Case 2 'Une seule
    'Activation du ComboBox avec Focus et déroulement
    cmbCategories.Enabled = True
    cmbCategories.SetFocus
    cmbCategories.Dropdown
  End Select
End Sub

Private Sub fraQuartet_AfterUpdate()
Dim strSQL As String
Dim strCategory As String

  Select Case Me!fraCategories
    Case 1 'Toutes
      Select Case fraQuartet
        Case 1 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 1]) AS [1er Trim]"
        Case 2 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 2]) AS [2e Trim]"
        Case 3 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 3]) AS [3e Trim]"
        Case 4 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 4]) AS [4e Trim]"
        Case 5 'Tous
          strSQL = "SELECT [Nom de catégorie],Sum([Trim 1]) AS [1er Trim], " & _
          "Sum([Trim 2]) AS [2e Trim],Sum([Trim 3]) AS [3e Trim],Sum([Trim 4]) AS [4e Trim]"
      End Select
      strSQL = strSQL & vbCrLf & "FROM reqPvtCommandesTrimestriellesParCatégorie"
      strSQL = strSQL & vbCrLf & "GROUP BY reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie];"
      ChartSalesByQuartet.RowSource = strSQL
      ChartSalesByQuartet.Requery
    Case 2 'Une seule
      cmbCategories.SetFocus
      If Len(Me!cmbCategories.Text) Then
        strCategory = Me!cmbCategories.Text
      Select Case fraQuartet
        Case 1 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 1]) AS [1er Trim]"
        Case 2 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 2]) AS [2e Trim]"
        Case 3 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 3]) AS [3e Trim]"
        Case 4 'Trimestre
          strSQL = "SELECT reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie], " & _
          "Sum(reqPvtCommandesTrimestriellesParCatégorie.[Trim 4]) AS [4e Trim]"
        Case 5 'Tous
          strSQL = "SELECT [Nom de catégorie],Sum([Trim 1]) AS [1er Trim], " & _
          "Sum([Trim 2]) AS [2e Trim],Sum([Trim 3]) AS [3e Trim],Sum([Trim 4]) AS [4e Trim]"
      End Select
        strSQL = strSQL & vbCrLf & "FROM reqPvtCommandesTrimestriellesParCatégorie"
        strSQL = strSQL & vbCrLf & "GROUP BY reqPvtCommandesTrimestriellesParCatégorie.[Nom de catégorie]"
        'Initialisation de la condition WHERE avec la catégorie
        strSQL = strSQL & vbCrLf & "HAVING ((([Nom de catégorie])=" & Chr(34) & _
        strCategory & Chr(34) & "));"
        ChartSalesByQuartet.RowSource = strSQL
        ChartSalesByQuartet.Requery
      Else
        MsgBox "Une catégorie doit être sélectionnée !", 48, "Catégorie requise"
        cmbCategories.SetFocus
        cmbCategories.Dropdown
      End If
  End Select

End Sub

Vous partez du même principe que pour réaliser l'histogramme en ce qui concerne le graphique dans le formulaire.
Vous démarrez alors l'Assistant graphique puis sélectionnez les rubriques idoines.

D'abord, vous choisissez la requête Analyse croisée que vous venez de créer :

Vous sélectionnez les champs comme l'illustre la figure ci-dessous :

Cliquez sur Suivant.

Vous choisissez le graphique de type Secteur 3-D.

Cliquez sur Suivant

Dans un premier temps, prenez l'année par défaut à savoir ici 1996. Maintenant que vous avez compris le principe de l'affichage par programme, il vous sera facile de procéder pour 1997 et les années suivantes puis l'ensemble des années…

Cliquez sur Suivant

Inscrivez enfin le titre approprié…

Cliquez sur Terminer

Le graphique réalisé est aussi affreux que non concordant que notre premier avant sa phase de modification…

Passez en mode création et modifiez son look comme je vous l'ai appris.

Cliquez deux fois sur une des parts pour afficher les propriétés, ici, celles pour afficher les étiquettes de légende et celles de pourcentage.

Et là, pour modifier la couleur et les motifs…

Il est inutile d'afficher une légende si vous affichez les étiquettes et inversement.

Vous devez alors obtenir ceci si vous avez opté pour ces couleurs :