Classer les résultats numériques par requêtes Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Classement des résultats avec Access

Dans cette formation Access, nous abordons les techniques permettant de classer les valeurs numériques. Il peut s'agir de chiffres d'affaires, de montants de commandes ou encore de moyennes générales par exemple. L'objectif consiste à attribuer des rangs aux valeurs comparées les unes avec les autres.



L'exemple de la capture ci-dessus illustre le résultat fourni par une requête. Cette dernière est munie d'un champ calculé mettant en oeuvre des fonctions permettant de classer les élèves, selon les moyennes obtenues.

Sources et présentation de la problématique
Nous proposons de récupérer une base de données composée d'une petite table.

• Télécharger la base de données classement-resultats.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Il s'agit d'une toute petite base de données destinée à tester les fonctionnalités Access permettant de réaliser des classements sur les valeurs numériques. C'est pourquoi seul un élément est proposé dans le volet des objets Access sur la gauche de la fenêtre. Il s'agit de la table moyennes.

• Double cliquer sur cette table pour accéder à son contenu en mode Feuille de données,

L'organisation est très simple. Les notes obtenues par des élèves dans différentes matières, désignées par des champs spécifiques, sont énumérées. Les deux derniers champs : MoyenneE et MoyenneD sont ceux qui retiennent notre attention. Le premier retranscrit les moyennes générales de chacun, converties en nombres entiers. Le second propose les moyennes originelles, en nombres décimaux. Ces résultats sont listés sous deux formes différentes, car le calcul du rang diffère selon la nature du nombre.

Classements sur des nombres entiers
Nous proposons de débuter par le cas le plus simple. Il s'agit de définir le classement de chaque élève par rapport à l'ensemble de la classe. L'élève ayant obtenu la meilleure moyenne générale sera classé premier. L'élève ayant obtenu la plus mauvaise moyenne sera classé dernier, soit huitième dans notre cas. Les tables sont les outils de construction de la base de données. Elles ne sont pas prévues pour accueillir des calculs. Les requêtes en revanche permettent de manipuler leurs informations pour préparer les données, en vue de la construction d'un formulaire par exemple. Et les requêtes peuvent accueillir des champs calculés. Ces derniers, comme leur nom l'indique, autorisent les formules dynamiques.

• Fermer la table moyennes en cliquant sur la croix de son onglet,
• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Requêtes, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue qui suit, sélectionner la table moyennes,
• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,

Nous accédons ainsi à l'éditeur de requêtes Access. La table moyennes est représentée dans sa version schématisée avec la liste de ses champs.

• Dans cette représentation schématique, cliquer sur le premier champ nommé ID,
• Tout en maintenant la touche MAJ (Shift) enfoncée, cliquer sur le dernier, nommé MoyenneD,

Nous englobons ainsi tous les champs dans la sélection.

• Glisser la sélection vers le bas sur la grille de requête de manière à les déposer,

Si nous exécutons la requête à ce stade, elle fournit exactement les mêmes résultats que la table moyennes. Elle consiste pour l'instant à sélectionner tous les champs et à restituer les informations pour chaque enregistrement. C'est ici que nous devons créer le champ calculé permettant de réaliser la moyenne de chaque élève, en fonction des notes du champ MoyenneE.

Un champ calculé se crée dans l'une des colonnes vides de la grille de requête. Il doit être nommé et son nom doit être suivi du symbole deux-points, pour annoncer la formule à suivre.

• Cliquer avec le bouton droit de la souris dans la zone Champ de la colonne vide située juste à droite du champ MoyenneD,
• Dans le menu contextuel, choisir Zoom,

Pour plus de confort, nous choisissons d'écrire la syntaxe de la formule dans le petit éditeur qui apparaît. La fonction Access qui permet de dénombrer tous les éléments d'un groupe (Champ de tous les enregistrements), est la fonction CpteDom. Si nous lui passons le critère qui consiste à compter, pour l'enregistrement en cours, tous ceux qui lui sont supérieurs, nous obtenons le rang. Pour la plus mauvaise moyenne par exemple, nous obtiendrons le résultat de 7, puisque 7 élèves sur les 8 ont une meilleure note. Si à ce résultat nous ajoutons une unité (+1), nous obtenons son classement soit huitième. La syntaxe de la fonction CpteDom est la suivante :

=CpteDom('[Champ_du_calcul]'; '[table]'; 'Critère_de_dénombrement)

• Dans l'éditeur zoomé, saisir l'expression suivante :

Rang:CpteDom('[MoyenneE]'; '[moyennes]'; '[MoyenneE]> ' & [MoyenneE])+1



Nous nommons ce nouveau champ Rang. Son calcul, après les deux points, exploite bien la fonction Access CpteDom. A cette dernière, nous passons le nom du champ pour le calcul en premier paramètre, soit MoyenneE. Comme vous le savez, dans une expression Access, un champ s'exprime entre crochets. En deuxième paramètre, nous lui passons le nom de la table (moyennes), également entre crochets. En troisième paramètre enfin, nous lui passons l'expression du critère à dénombrer sur l'ensemble des enregistrements. Il s'agit d'une partie fixe concaténée (&) à une partie variable. La partie fixe désigne le champ MoyenneE pour l'enregistrement en cours. Tous seront passés en revue par cette fonction, à l'instar de la fonction Nb.Si d'Excel par exemple, qui permet de dénombrer toutes les cellules répondant à un critère. Nous vérifions si sa valeur est supérieure (>) à l'ensemble des autres valeurs pour ce même champ, fourni en partie variable donc (& [MoyenneE]). Nous incrémentons ce résultat d'une unité pour obtenir le classement, comme nous l'avons expliqué plus haut.

• Valider l'expression en cliquant sur le bouton Ok de l'éditeur zoomé,

De retour sur la grille de requête, vous constatez la présence du champ calculé à l'emplacement que nous avons choisi.

• Enregistrer la requête (CTRL + S) sous le nom : ClassementE,
• Puis, valider par Ok,
• Cliquer ensuite sur le bouton Exécuter du ruban Créer par exemple,

Nous obtenons une vue semblable à celle de la table. Mais à l'extrémité droite, vous notez la présence du champ Rang, tel que nous l'avons nommé. Et notre calcul a parfaitement fonctionné puisqu'un classement cohérent est attribué à chaque élève, en fonction de sa moyenne générale convertie en nombre entier.



Pour y voir plus clair, il aurait été pertinent de réaliser un tri décroissant sur le champ MoyenneE. Les requêtes permettent de manipuler les données avec beaucoup de puissance. Nous proposons donc de réaliser ce petit réglage.

• Cliquer sur la flèche du bouton Affichage dans le ruban Accueil,
• Dans la liste, choisir Mode création,
• Dans la zone Tri du champ calculé Rang, dérouler la liste déroulante,
• Choisir Croissant et enregistrer la requête,

Si vous exécutez de nouveau la requête, vous obtenez des données triées du meilleur au plus mauvais élève. Nous aurions aussi pu engager un tri décroissant sur le champ de la moyenne. L'important est de pouvoir réaliser un tri consistant à restituer les classements dans l'ordre.

Classer les nombres décimaux
Nous proposons de créer une deuxième requête sur le modèle de la première. A priori, le calcul est identique mais doit désormais porter sur le champ MoyenneD.

• Fermer la requête ClassementE,
• Dans le volet des objets Access sur la gauche, cliquer avec le bouton droit sur la requête,
• Dans le menu contextuel, choisir Copier,
• Puis, cliquer sur un emplacement vide du volet, toujours avec le bouton droit,
• Dans le menu contextuel, choisir Coller,
• Dans la petite fenêtre qui suit, la renommer ClassementD et valider par Ok,

La nouvelle requête, identique à la première, apparaît dès lors listée dans le volet des objets Access. Nous n'avons plus qu'à adapter l'expression de son champ calculé.

• Cliquer avec le bouton droit de la souris sur la requête ClassementD,
• Dans le menu contextuel, choisir Mode création,
• Dans la grille de requête, cliquer avec le bouton droit sur le champ calculé Rang,
• Dans le menu contextuel, choisir Zoom,
• Dans l'expression, remplacer le champ MoyenneE par le champ MoyenneD,

Trois modifications sont à entreprendre.

• Valider par Ok et enregistrer la requête,
• Puis, l'exécuter,

Deux cas de figure peuvent se présenter selon la version d'Access. Soit la requête s'affiche et des erreurs apparaissent dans le champ calculé, en regard des moyennes décimales. Soit comme ici, le message d'erreur intervient en amont et n'autorise pas l'affichage de la requête. Le message est explicite. La virgule est en cause. En effet, Access est un logiciel américain pour qui la décimale est matérialisée par le symbole du point (.) et non de la virgule (,). En conséquence, dans notre expression de calcul, les nombres sont considérés comme du texte. Et il n'est pas possible de réaliser des opérations arithmétiques sur des chaînes de caractères.

Nous proposons deux solutions pour palier le problème. La première consiste à exploiter les fonctions offertes par le générateur d'expression. L'une d'entre elles se nomme Remplacer. Comme son nom l'indique, elle permet de remplacer une occurrence (La virgule) par une autre (Le point), dans une chaîne de caractères (La moyenne décimale). Sa syntaxe est la suivante :

Remplacer(Chaine_de_texte ; Caracatère_à_remplacer ; Caractère_de_remplacement)

Comme il s'agit d'une fonction manipulant des chaînes de caractères, elle est susceptible de générer une erreur, selon la version d'Access. Auquel cas, nous pourrions l'encadrer dans une fonction de conversion telle que CDbl. Cette dernière convertit l'expression qui lui est passée en nombre réel. Mais nous proposons une méthode universelle en seconde solution, grâce à un tout petit bout de code VBA Access.

• Dans la grille de requête, cliquer avec le bouton droit sur le champ calculé Rang,
• Dans le menu contextuel, choisir Créer,
• Dans le générateur d'expression qui suit, cliquer juste devant le dernier champ [MoyenneD], situé après le caractère de concaténation (&),

Nous définissons ainsi l'emplacement à partir duquel doit être insérée la fonction de traitement.

• Dans la liste des éléments d'expression, déployer l'affichage de la rubrique Fonctions,
• Puis, sélectionner l'élément Fonctions intégrées,
• Dans les catégories d'expressions au centre, sélectionner la catégorie Texte,
• Dans les valeurs d'expression sur la droite, double cliquer sur la fonction Remplacer,

Celle-ci s'insère en effet devant le champ MoyenneD mais ne l'encadre pas. L'expression à ce stade est la suivante :

Rang:CpteDom('[MoyenneD]'; '[moyennes]';'[MoyenneD] >' & Remplacer(«string»; «find»; «replace»; «start»; «count»; «compare») «Expr» [MoyenneD])+1

• Modifier l'expression comme suit :

Rang:CpteDom('[MoyenneD]'; '[moyennes]'; '[MoyenneD] >' & Remplacer( [MoyenneD]; ','; '.'))+1

Le champ MoyenneD doit être passé en premier argument de la fonction Remplacer. La virgule à remplacer doit être exprimée en deuxième argument, entre guillemets car il s'agit d'un texte. Le point doit être exprimé en troisième argument, lui aussi entre guillemets. Il ne faut pas oublier de fermer la parenthèse de la fonction Remplacer. Nous obtenons donc deux parenthèses fermantes juste avant l'incrémentation d'une unité (+1).



La capture ci-dessus illustre le résultat final, avec toutes les étapes intermédiaires réalisées dans le générateur d'expression Access.

• Valider la modification de la formule en cliquant sur le bouton Ok,
• Enregistrer la requête (CTRL + S),
• Puis, cliquer sur le bouton Exécuter du ruban Créer pour visualiser son résultat,

Cette fois, nous obtenons les classements tels qu'ils avaient été obtenus par la première requête sur les nombres entiers. Mais vous en conviendrez, réaliser des classements sur des nombres décimaux s'est avéré plus périlleux. Il est intéressant d'avoir rencontré ce cas particulier. Nous saurons désormais que pour traiter des nombres réels par les fonctions Access, il convient dans un premier temps, de remplacer la virgule par le point.

Exécuter une fonction VBA dans une requête Access
Nous allons découvrir une méthode permettant d'actionner de nouveaux leviers et d'offrir de nouvelles perspectives dans l'exploitation d'Access. Il s'agit de concevoir une requête classiquement, comme nous l'avons entrepris jusqu'alors. Mais pour des traitements spécifiques, nous pouvons passer la main à un code Visual Basic Access. Le principe est de créer une nouvelle fonction VBA pour remplacer la virgule par le point. Dans l'expression du champ calculé, il suffira d'appeler cette fonction par son nom, afin de traiter les nombres décimaux du champ MoyenneD.

Tout d'abord, nous proposons de dupliquer la dernière requête. Nous conserverons ainsi l'historique des fonctionnalités ayant permis de calculer les classements avec succès.

• Fermer la requête ClassementD en cliquant sur la croix de son onglet,
• Dans le volet des objets Access, cliquer avec le bouton droit de la souris sur cette dernière,
• Dans le menu contextuel, choisir Copier,
• Puis, cliquer avec le bouton droit sur un emplacement vide du volet,
• Dans le menu contextuel, choisir Coller,
• Dans la fenêtre qui suit, la renommer ClassementDF et valider par Ok,
• Cliquer ensuite sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• A droite du ruban, dans la section Macros et code, cliquer sur le bouton Module,

Nous basculons ainsi dans l'éditeur de code Visual Basic. Comme nous l'ont appris les différentes formations VBA Access, une fonction permet de réaliser un traitement et de retourner le résultat à l'appelant. C'est le mot clé Function qui permet de la déclarer.

• Dans la feuille de code au centre de l'écran, créer la fonction nb_fr comme suit :

Function nb_fr(chaine As Variant)

nb_fr = Replace(chaine, ',', '.')

End Function

Cette fonction est triviale. Elle ne requiert pas de compétence en développement. Nous la nommons nb_fr. Nous la déclarons avec un paramètre en attente (chaine). Nous le déclarons comme un Variant pour autoriser le traitement sur le plus grand nombre. Il s'agit du nombre décimal à traiter. Nous exploitons la fonction VBA Replace qui, comme son homologue Remplacer du générateur, permet de substituer une occurrence par une autre, dans un texte. Nous affectons son résultat à la fonction elle-même, par son nom (nb_fr). C'est ainsi en VBA qu'une fonction retourne son traitement.

• Enregistrer les modifications en conservant le nom Module1 attribué par défaut,
• Basculer sur l'application Access avec le raccourci clavier ALT + F11 par exemple,
• Cliquer avec le bouton droit de la souris sur la requête ClassementDF,
• Dans le menu contextuel, choisir Création,
• Dans l'éditeur de requête, cliquer avec le bouton droit sur le champ calculé Rang,
• Dans le menu contextuel, choisir Zoom,
• Dans la fenêtre qui suit, adapter l'expression avec la fonction VBA comme suit :

Rang:CpteDom('[MoyenneD]'; '[moyennes]';'[MoyenneD] >' & nb_fr([MoyenneD]))+1

Notre fonction VBA nb_fr ne requiert qu'un seul paramètre. Les arguments de la fonction Remplacer doivent donc être supprimés.

• Valider l'expression en cliquant sur le bouton Ok,
• Enregistrer la requête (CTRL + S),
• Puis, cliquer sur le bouton Exécuter du ruban Créer,

Comme vous le remarquez, nous obtenons exactement le même résultat qu'avec la requête ClassementD. Nous sommes parvenus à organiser les nombres avec décimales, grâce à l'appel d'une petite fonction VBA Access depuis une requête. Ces imbrications peuvent apporter beaucoup de puissance à l'avenir, pour le développement d'applications professionnelles avec Access.