Opérations de synthèse par les 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 :


Exercice Access sur les requêtes de synthèse

Dans ce troisième exercice Access sur les requêtes, après les requêtes sélection et les requêtes paramétrées, nous abordons les requêtes de synthèse. Ces dernières permettent de réaliser des regroupements sur les données pour des besoins statistiques. Elles offrent donc la possibilité de tirer des interprétations efficaces.

Base de données source
Au fil de l'apprentissage, nous avons enrichi notre base de données d'un certain nombre d'objets Access. Il s'agit donc de la récupérer dans un premier temps, afin de poursuivre sa construction.

• Télécharger la base clients-et-commandes-requetes-synthese.accdb en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Le volet des objets Access, sur la gauche de l'écran, énumère les tables et les requêtes de cette base de données. Comme vous le remarquez, ces éléments sont regroupés par catégories, afin de les identifier plus facilement.

• Dans ce volet, cliquer droit sur la requête R_Quels_dep_et_civ,
• Dans le menu contextuel, choisir Mode création,

Nous y retrouvons les réglages de l'une de nos dernières requêtes paramétrées. L'astuce avait consisté à insérer un champ nom reconnu entre crochets, dans la zone de critères. De fait à l'exécution, Access demande à l'utilisateur de préciser la valeur. Ainsi, nous avions obtenu une requête dynamique permettant d'extraire les personnes d'une civilité à définir dans un département à préciser.

Requête Analyse Croisée
A l'instar des tableaux dynamiques d'Excel, Access offre un assistant pour croiser des informations et réaliser des opérations de synthèse sur les données recoupées. Il s'agit de la requête analyse croisée. Pour réaliser de telles analyses, encore faut-il que les données s'y prêtent. Lorsque les tables Commandes et Detail_commandes seront renseignées, cet outil s'avèrera fort précieux. Nous pourrons par exemple réaliser des études pour connaître les produits les plus en vogue, soit les mieux vendus. Les résultats permettront à l'entreprise d'ajuster ses stratégies commerciales.

Néanmoins, sur la base des informations dont nous disposons à ce stade, nous proposons de découvrir cet outil puissant. Nous comprendrons son mécanisme pour l'exploiter à bon escient dès que l'occasion se présentera.

Pour l'exemple, à partir de la table Produits, nous souhaitons regrouper tous les articles dont le stock est identique. Ce regroupement permettra de compter le nombre total de références par quantités restantes. Si les quantités faibles présentes une valeur importante, nous saurons que des réapprovisionnements sont à envisager d'urgence.

• Fermer la requête R_Quels_dep_et_civ en cliquant sur la croix de son onglet,
• 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 Assistant Requête,
• Dans la boîte de dialogue, choisir Assistant Requête analyse croisée,

Nous avons déjà exploité l'assistant pour créer une requête sélection simple. Nous aurons l'occasion d'y revenir pour découvrir l'intérêt des requêtes Trouver les doublons et non-correspondance.

• Cliquer sur le bouton Ok pour démarrer l'assistant,
• Dans l'étape qui suit, sélectionner la table Produits et cliquer sur le bouton Suivant,
• Dans cette nouvelle étape, sélectionner le champ produit_stock,
• Puis, cliquer sur le bouton de la flèche orientée à droite,

Nous débutons la construction de notre requête analyse croisée. Comme l'indique l'assistant, par ce champ, nous définissons quelles sont les informations que nous souhaitons regrouper et présenter en ligne.

• Cliquer de nouveau sur le bouton Suivant,
• Dans cette nouvelle étape, désigner le champ produit_poids,

Nous désignons ainsi les informations à regrouper en colonnes et à croiser avec les précédentes données sur la quantité en stock en lignes.

• Cliquer une fois encore sur le bouton Suivant pour progresser dans la construction,
• Conserver le champ produit_ref et la fonction Compte,

En effet, comme pour un tableau croisé dynamique avec Excel, nous définissons l'opération de synthèse à afficher au croisement des champs recoupés. Nous choisissons l'opération du dénombrement avec la fonction Access Compte. Ce décompte s'opère naturellement sur le champ de la clé primaire, celui des références des articles. Elles sont uniques par définition. Ainsi, nous souhaitons compter le nombre d'articles du même poids et possédant la même réserve en stock.

• Cliquer une dernière fois sur le bouton Suivant pour atteindre la dernière étape,
• Nommer la requête : Synthese_ref_qte_poids et cliquer sur le bouton Terminer,

L'analyse croisée de la requête Access est aussitôt livrée en mode feuille de données. Et comme vous le remarquez, aussi simplement qu'avec un tableau croisé dynamique, nous pouvons consulter les données synthétisées. Tout d'abord, grâce au champ de synthèse Total de produit_ref, nous remarquons instantanément que les produits possédant un stock faible sont majoritaires. Ensuite, par catégories de poids, nous obtenons le détail de la répartition. Ces informations statistiques sont très intéressantes pour avoir une idée globale de l'état des marchandises en stock. En outre, elles doivent permettre, quitte à amorcer d'autres synthèses intermédiaires, d'entreprendre les actions appropriées.

Requêtes et recoupement
Désormais, nous proposons de découvrir comment il est possible de mettre en oeuvre ces opérations, dans une requête sélection classique. Comme vous le savez et comme nous l'avons évoqué dans les exercices précédents, la table Communes propose des redondances. A l'avenir, nous purgerons ces doublons à l'aide de requêtes action que nous apprendrons à domestiquer. Mais pour l'instant, nous proposons de bâtir une requête capable de compter le nombre de redondances pour chaque ville répliquée. Nous devons donc regrouper l'information sur le nom de la commune et pour chaque doublon identifié, nous devons engager un décompte. Le tout doit être livré dans une vue en mode feuille de données.

• Fermer la requête Synthese_ref_qte_poids en cliquant sur la croix de son onglet,
• 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 la table Communes,
• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,
• Dans la représentation de la table, double cliquer sur le champ Commune_nom,
• Double cliquer ensuite sur le champ Commune_id,

Ainsi, nous les ajoutons tous deux sur la grille de requête.

• Cliquer droit sur un emplacement vide de la grille de requête,
• Dans le menu contextuel, choisir Totaux,

C'est ainsi que nous allons pouvoir exploiter une requête sélection classique pour livrer des résultats statistiques. Et à ce titre, vous notez l'apparition de la ligne Opération. Par défaut, elle propose de réaliser le regroupement pour les deux champs. Nous souhaitons en effet regrouper les villes communes. Le réglage est donc cohérent pour le champ Commune_nom. Mais pour chacune, nous souhaitons compter le nombre de redondances identifiées.

• Dans la zone Opération du champ Commune_id, choisir Compte à la place de Regroupement,
• Dans sa zone Tri, régler la valeur sur Décroissant,

De cette manière, nous mettrons en évidence les villes proposant le plus grand nombre de doublons.

• Enregistrer la requête (CTRL + S) sous le nom : RS_doublons_communes,

A validation, elle apparaît dans le volet des objets Access, classée dans la catégorie Requêtes, par ordre alphabétique.

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

Les résultats tombent instantanément. La table Communes propose 1003 enregistrements. Cette requête statistique affiche 807 résultats. Les doublons sont en effet regroupés. Et pour chacun, vous notez le score en regard.



L'étendue des dégâts est explicite. Instantanément, les communes incriminées sont mises en évidence selon l'ampleur de la répétition. La ville d'Hyères est répétée pas moins de 10 fois. Des travaux de nettoyage sont à entreprendre rapidement.

Bien que ces requêtes de synthèse soient spécifiques, tout ce que nous avons appris jusqu'alors reste vrai. Cela signifie que nous pouvons croiser ces opérations avec des critères et ça tombe bien. Dans cette vue statistique, seules les villes offrant des anomalies nous intéressent. Il n'est pas utile de comptabiliser les communes sans redondances. En d'autres termes, nous devons enclencher le décompte seulement si des redondances sont détectées.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création pour modifier la requête,
• Dans la zone Critères du champ Commune_id, ajouter la condition suivante : >1 ,

De la sorte, nous devrions obtenir une vue purgée des villes uniques.

• Enregistrer la modification et cliquer sur le bouton Exécuter dans le ruban Créer,

Cette fois, il ne reste plus que 122 enregistrements. Cette nouvelle information est importante. Nous comprenons que 122 villes proposent des doublons. Et elles sont toujours organisées par niveau de redondance le plus élevé.

Requête de synthèse paramétrée
Nous le disions, ce que nous avons appris reste vrai. Donc, nous pouvons vraisemblablement engager des opérations selon un critère défini dynamiquement par l'utilisateur. Ce sont les requêtes paramétrées qui interrogent l'opérateur au moment de l'exécution. Ainsi, nous souhaitons construire une requête dynamique capable de compter tous les clients habitant dans un département à définir. A chaque exécution, elle peut fournir un résultat différent si le département inscrit n'est pas le même.

• Fermer la requête RS_doublons_communes en cliquant sur la croix de son onglet,
• Dans le ruban Créer, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue, choisir la table Clients,
• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,
• Dans la représentation de la table, double cliquer sur les champs Client_id et Client_dep,

Nous les ajoutons ainsi sur la grille de requête. Fort naturellement, le décompte doit être réalisé sur le champ proposant des valeurs uniques. Le champ Client_id est le champ de la clé primaire. Le critère doit être posé dynamiquement sur le champ du code postal. Aucun n'est similaire. Le regroupement ne peut donc pas être envisagé sur ce champ. Mais certains appartiennent au même département. C'est ce qui nous intéresse.

• Cliquer droit sur un emplacement vide de la grille de requête,
• Dans le menu contextuel, choisir Totaux,

Comme précédemment, nous faisons apparaître la ligne Opération. Par défaut, elle propose de regrouper les valeurs de champs.

• Sur la ligne Opération du champ Client_id, remplacer Regroupement par Compte,

A ce titre, vous remarquez que la liste déroulante propose d'autres opérations de synthèse comme la somme ou la moyenne. Ces outils seront intéressants à exploiter à l'avenir pour synthétiser tous les achats d'un client, ou pour un produit particulier par exemple.

• Dans la ligne Opération du champ Client_dep, remplacer Regroupement par Où,

Comme nous le disions, il n'y a pas de regroupement à effectuer sur ce champ. Nous devons enclencher un décompte sur le champ Client_id lorsqu'un critère (Où) est vérifié sur le champ Client_dep.

• De fait, dans la zone Critères du champ Client_dep, ajouter l'expression suivante :

Comme [Quel département ?] & '*'

Nous répliquons strictement à l'identique une expression que nous avons démontrée dans l'exercice sur les requêtes paramétrées. L'expression tapée entre crochets est destinée à leurrer Access. Ne reconnaissant pas le nom du champ désigné, il va proposer à l'utilisateur de préciser la valeur. Ce département est alors réceptionné et intégré dans l'expression concaténée. Grâce à l'opérateur Comme et à l'étoile en suffixe, nous demandons de réaliser le décompte sur les codes postaux préfixés des deux chiffres renseignés par l'opérateur.

• Enregistrer la requête sous le nom : RS_nb_clients_dep,
• Puis, cliquer sur le bouton Exécuter dans le ruban contextuel Créer,

Une boîte de dialogue se déclenche en effet avec pour message l'indication tapée judicieusement entre crochets.

• Saisir le nombre 83 par exemple pour le département du Var,
• Puis valider par Ok,

Fort naturellement, du fait de la présence du critère, seule une ligne d'information est restituée. Elle indique que onze clients habitent le département du Var. Si vous l'exécutez de nouveau en renseignant un autre département, comme le 06, vous obtenez fort logiquement un résultat de synthèse différent.

Ces requêtes de synthèse dynamiques sont donc fort précieuses pour fournir rapidement des renseignements importants, au moment souhaité. Leur utilisation peut se décliner dans de nombreux contextes utiles. Nous pourrions par exemple bâtir une requête de synthèse paramétrée pour connaître le nombre de produits aux stocks faibles. Le décompte doit être réalisé sur le champ produit_ref de la table Produits. Le critère doit être posé sur le champ produit_stock.



Requête de synthèse et champ calculé
Dans toute requête, il est possible de créer un champ qui n'appartient à aucune table. Ce champ est destiné à réaliser un calcul spécifique non prévu par les opérations proposées par défaut. Il s'agit des champs calculés. Avec les données dont nous disposons, nous proposons par exemple de calculer la masse financière des produits recensés sous une même quantité en stock. Nous saurons ainsi, si l'approvisionnement à entreprendre est conséquent, lorsque les quantités sont faibles.

L'opération consiste à multiplier le prix unitaire de l'article par sa quantité restante, par tranches de stocks regroupés. Grâce à cette démonstration, nous pourrons bâtir des requêtes intéressantes à l'avenir. Elles nous permettront de calculer le chiffre d'affaires total généré par un client ou sur un article en particulier par exemple.

• Dans le ruban Créer, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue, choisir la table Produits,
• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,
• Dans la représentation de la table, double cliquer sur le champ produit_stock,
• Cliquer droit sur un emplacement vide de la grille de requête,
• Dans le menu contextuel, choisir Totaux,

Par défaut, l'opération de regroupement est proposée sur le champ produit_stock. C'est exactement ce que nous souhaitons pour les réunir par tranches de quantités. Pour chaque tranche, nous souhaitons calculer la masse financière.

• A droite du champ produit_stock, créer le champ valeur_M, comme suit :

valeur_M:[produit_prix]*[produit_stock]

Un champ calculé est toujours défini par son nom suivi du symbole des deux points (:). Ensuite, le calcul doit suivre. Pour chaque produit du même groupe de quantité, nous multiplions son prix par sa quantité en stock. Et comme vous le savez, dans la syntaxe Access, chaque champ intervenant dans le calcul doit être encadré de crochets.

• Puis, sur sa ligne Opération, choisir la Somme,

En effet, pour chaque article identifié dans le même groupe, nous devons additionner sa valeur marchande aux autres, calculée par l'expression que nous avons saisie dans la zone Champ.

• Enregistrer la requête sous le nom : RS_valeurs_stocks, puis valider,
• Ensuite, cliquer sur le bouton Exécuter dans le ruban contextuel Créer,

Comme vous le voyez, grâce à ce champ calculé, nous obtenons une bonne représentation des valeurs marchandes par quantités regroupées. Et à ce titre, nous constatons que les faibles quantités (6 unités) représentent une importante masse financière qu'il va falloir songer à réapprovisionner.

A l'avenir, nous exploiterons tous ces acquis sur les requêtes afin de produire des résultats statistiques sur nos formulaires qui constituent l'un des aboutissements d'une application Access.

Dans le prochain exercice, nous aborderons la syntaxe SQL des requêtes, particulièrement précieuse pour développer encore plus de puissance.