Classements conditionnels avec une requête Access

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


Sujets que vous pourriez aussi aimer :


Classements avec critère

Access permet de manipuler, d'extraire et d'organiser les données. Parmi les nombreuses fonctions de calcul qu'il propose, l'une d'entre elles permet d'effectuer des classements. Ces résultats statistiques sont intéressants pour connaitre les lauréats ou les meilleurs éléments d'une société par exemple. Cette nouvelle astuce Access montre comment réaliser ces calculs de rang.

Source et procédure
Nous proposons d'agir à partir d'une source de données présentant les ventes réalisées par des commerciaux.

• Télécharger le fichier classements-avec-critere.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Comme vous pouvez le voir en consultant le volet de navigation, cette petite base n'est constituée que de deux tables nommées respectivement : Commerciaux et Ventes.

• Double cliquer sur la table Commerciaux pour l'afficher en mode Feuille de données,

26 vendeurs y sont référencés. Vous notez qu'ils n'agissent pas tous sur la même zone comme l'indique le champ c_secteur. Chaque commercial est affecté à un département. Il s'agira donc d'effectuer un classement global puis un classement par secteur. Mais pour cela, nous avons besoin de connaître la somme des chiffres réalisés par chacun.

• Double cliquer sur la table Ventes pour l'afficher en mode Feuille de données,

Cette table archive beaucoup plus d'enregistrements. Ils sont au nombre de 104. La raison est simple, elle stocke toutes les ventes réalisées par chaque commercial. Ces dernières ne sont donc pas encore consolidées. Les montants respectifs sont énumérés dans le champ v_montant. Et chaque chiffre est attaché à un vendeur de la table Commerciaux par le numéro de clé externe v_id. Ce numéro est celui qui identifie de façon unique le vendeur dans la table parente.

Consolider les ventes
Avant de pouvoir réaliser des calculs de rang, nous devons consolider les chiffres effectués. Cela signifie que nous devons additionner toutes les ventes sur les commerciaux regroupés. Il s'agit d'une routine pour Access qui permet d'exécuter ces opérations de synthèse avec grande simplicité.

• Fermer les deux tables en cliquant sur les croix des onglets,
• En haut de la fenêtre Access, cliquer sur l'onglet Créer pour activer son ruban,
• Dans la section Requêtes du ruban, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue qui suit, sélectionner les deux tables avec la touche CTRL,
• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,

Nous les ajoutons ainsi dans l'éditeur de requête sous leur forme schématisée.

• Sélectionner tous les champs de la table Commerciaux, sauf le champc_num,
• Puis, les glisser sur la grille de requête,

Nous les ajoutons ainsi dans la liste des informations à extraire.

• Puis, double cliquer sur le champ v_montant de la table Ventes,

Nous l'insérons ainsi à la suite dans l'énumération des champs de la requête. Si vous exécutez cette requête à ce stade, elle offre un rendu de 104 enregistrements, soit le même nombre que celui de la table Ventes. En effet, elle se contente pour l'instant de restituer chaque vente isolée avec le nom du commercial associé. Et celui-ci est répété autant de fois qu'une vente lui est attachée. Nous devons faire en sorte que tous les chiffres d'un commercial soient consolidés. Nous profiterons ainsi de la synthèse et son nom n'apparaîtra qu'une seule fois. Pour cela, nous devons déclencher les opérations dans la requête.

• Cliquer avec le bouton droit de la souris n'importe où dans la grille de requête,
• Dans le menu contextuel, choisir Totaux,

Cette action a pour effet d'ajouter la ligne Opération dans la grille de requête. Et pour chaque champ, c'est un regroupement qui est ordonné. Toutes les informations répétées pour un commercial doivent en effet être regroupées. Mais pour un même commercial, tous ses chiffres doivent être cumulés.

• Déployer la liste déroulante de la zone Opération pour le champ v_montant,
• Dans les propositions d'opérations, choisir la somme,

Désormais, c'est le chiffre global réalisé par commercial qui doit apparaître.

• Enregistrer la requête (CTRL + S) sous le nom r_ventes,
• Puis, cliquer sur le bouton Exécuter tout à fait à gauche dans le ruban contextuel Créer,

Cette fois, l'extraction de la requête ne livre plus que 26 enregistrements, soit le même nombre que celui de la table Commerciaux. Pour chaque commercial, nous obtenons bien le montant consolidé de ses ventes dans le dernier champ.

Calculer les rangs
Désormais, nous souhaitons classer ces commerciaux en fonction de leurs résultats. Celui ayant réalisé le plus gros chiffre devra être classé en premier, donc avec le chiffre 1, puis les autres suivront. Il s'agira même de pouvoir établir des classements par secteur, donc à l'intérieur de chaque département. Pour cela, nous devrons croiser un critère sur ce calcul. Avec Access, c'est la fonction de dénombrement CpteDom qui permet d'établir ces rangs lorsqu'une condition est judicieusement posée. Son équivalent SQL est la fonction DCount. En premier argument, nous devons lui indiquer sur quel champ poser l'analyse. Ici, il s'agit du champ SommeDev_montant de notre requête. En deuxième argument, nous devons lui stipuler le nom de la table ou de la requête ici en l'occurrence. En dernier argument, nous devons vérifier quelles sont les sommes supérieures sur le champ des montants consolidés. S'il n'y en a aucune, nous en concluons que le commercial est bien le meilleur.

• Fermer la requête r_ventes en cliquant sur la croix de son onglet,
• Dans la section Requêtes du ruban Créer, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue qui suit, activer l'onglet Requêtes,
• Double cliquer sur la requête r_ventes pour l'ajouter dans l'éditeur,
• Puis, cliquer sur le bouton Fermer de la boîte de dialogue,

Pour établir la synthèse de ces classements, en plus du champ calculé à construire avec la fonction CpteDom, nous souhaitons restituer toutes les autres informations.

• Dans la vue schématisée de la requête, sélectionner tous les champs,
• Puis, les glisser sur la grille de requête,
• Enregistrer (CTRL + S) cette requête sous le nom r_rangs,
• Elargir la première colonne vide après l'énumération des champs,

Ainsi, nous faisons de la place pour accueillir la syntaxe du champ calculé à construire.

• Cliquer droit dans la zone Champ de cette nouvelle colonne,
• Dans le menu contextuel, choisir Créer,

Ainsi, nous affichons le générateur d'expression. Nous n'allons pas faire appel à sa bibliothèque. Mais étant donné que la syntaxe est relativement longue, cette vue est plus confortable.

• Taper l'intitulé cl suivi du symbole deux points, soit : cl:,

De cette manière, nous nommons le champ calculé que nous sommes en train de créer, cl pour classement. Et comme vous le savez, tout nom de champ calculé doit être suffixé du symbole deux points.

• Appeler la fonction de dénombrement suivi d'une parenthèse, soit : CpteDom(,
• Désigner le champ des ventes entre guillemets et entre crochets : "[SommeDev_montant]" ,
• Taper un point-virgule (;) pour passer dans l'argument de la table du décompte,
• Désigner la requête r_ventes entre guillemets et entre crochets, soit : "[r_ventes]",
• Taper un point-virgule (;) pour passer dans l'argument du critère à honorer pour le décompte,
• Puis, taper l'expression suivante: "[SommeDev_montant]> " &[SommeDev_montant],

De cette manière, pour chaque enregistrement, nous cherchons à connaître le nombre de montants consolidés supérieurs à celui du commercial en cours d'analyse. S'il n'y en a aucun, le décompte renvoie zéro et nous savons qu'il s'agit du commercial à placer en premier. Nous devons donc ajouter une unité pour corriger le décalage de ce dénombrement. Ainsi zéro passera à un pour afficher le rang concordant. Juste avant cela, nous commentons une petite particularité dans la syntaxe. Le champ pour l'enregistrement en cours et mentionné entre guillemets. Pour qu'il soit comparé à toutes les autres valeurs de ce même champ, nous lui concaténons sa partie dynamique, donc sans les guillemets.

• Fermer la parenthèse de la fonction CpteDom,
• Puis, ajouter une unité au décompte, soit : +1,
• Cliquer sur le bouton Ok de la boîte de dialogue pour valider l'expression,
• De retour dans l'éditeur de requête, enregistrer les modifications (CTRL + S),
• Puis, cliquer sur le bouton Exécuter dans le ruban Créer,

Les rangs sont tous calculés et semblent parfaitement cohérents. La syntaxe du champ calculé que nous avons construit est la suivante :

cl:CpteDom("[SommeDev_montant]"; "[r_ventes]"; "[SommeDev_montant]> " &[SommeDev_montant])+1

Cependant, en l'absence d'organisation des données, il est compliqué d'obtenir une bonne représentation de ces classements à première vue. Pour corriger ce défaut, l'astuce est toute simple. Elle consiste à organiser un tri décroissant sur le champ des ventes consolidées ou encore un tri croissant sur le champ calculé que nous venons de construire.

• A gauche du ruban Accueil, cliquer sur le bouton Affichage pour revenir en conception,
• Double cliquer dans la zone tri du champ calculé cl,

Nous ordonnons ainsi un tri croissant sur les rangs.

• Enregistrer la modification,

Il ne nous reste plus qu'a apprécier le résultat.

• Cliquer sur le bouton Exécuter dans le ruban Créer,

Comme vous pouvez le voir, cette simple réorganisation améliore considérablement l'efficacité des interprétations. Désormais, nous souhaitons bonifier cette astuce pour offrir des classements par département. C'est une façon de comparer les informations dans leur contexte. Pour cela, nous devons émettre un critère. Ce dernier doit consister à prélever le département choisi par l'utilisateur au moment de l'exécution de la requête.

• Cliquer sur le bouton Affichage du ruban Accueil pour revenir en conception de la requête,
• Compléter l'expression du champ calculé comme suit :

cl:CpteDom("[SommeDev_montant]"; "[r_ventes]"; "[SommeDev_montant]>" & [SommeDev_montant] & " AND [c_secteur] = '" & [Dep] & "'")+1

Dans la clause Where de la fonction CpteDom, nous recoupons la première condition avec un critère sur le département. Pour cela nous utilisons l'opérateur AND. La partie statique de l'expression est exprimée entre doubles côtes. Nous vérifions l'égalité sur le département (c_secteur) avec la valeur entrée par l'utilisateur au moment de l'exécution ([Dep]). Souvenez-vous, c'est le principe des requêtes paramétrées. Le champ Dep que nous mentionnons entre crochets n'existe pas. En conséquence, Access va demander de préciser la valeur au moment de l'exécution. L'information sur le département est typée comme un texte. C'est la raison de l'alternance des simples et doubles côtes. Les doubles côtes servent à encadrer les parties statiques. Les simples encadrent de guillemets la valeur textuelle du département transmis.

• Enregistrer les modifications et exécuter la requête,

Comme nous le disions, une précision est demandée sur la valeur du critère à appliquer sur le champ c_secteur.

• Taper par exemple le département 83 puis valider par Ok,

En Feuille de données, tous les enregistrements persistent fort naturellement. Le critère supplémentaire de la fonction CpteDom est utilisé pour exercer le calcul. Il n'agit pas comme un filtre. Pour y voir plus clair donc, il est conseillé d'organiser un tri décroissant sur les départements du champ c_secteur.



Et comme vous pouvez le voir, à l'intérieur même de ce département ciblé, le classement est parfaitement cohérent. Nous obtenons bien les positions relatives et non plus absolues.

Mais pour une interprétation plus pertinente, il serait plus judicieux d'isoler ce classement des autres résultats. Pour cela, il s'agit de répéter le même critère paramétré sur le champ c_secteur.

• Cliquer sur le bouton Affichage dans le ruban Accueil pour revenir en conception,
• Dans la zone Critères du champ c_secteur, inscrire de nouveau le champ inconnu : [Dep],

Bien qu'inconnu, comme ce champ est le même que celui utilisé dans la condition de la fonction CpteDom, la précision ne sera demandée qu'une fois. Et nous allons le constater.

• Enregistrer les modifications et exécuter la requête,
• A l'invite, taper le département 06 par exemple puis valider,

Les résultats s'affichent instantanément en effet. Et comme vous pouvez le voir, les commerciaux agissant sur le département demandé sont isolés des autres. De plus, le classement relatif est parfaitement opéré.