Les requêtes avec regroupement

Opérations « Compte » et « Somme »

f-leb

Cet article se propose de faire découvrir les requêtes avec regroupement sous Access pour effectuer des opérations élémentaires et fréquentes de comptage et d'addition. L'approche se veut didactique et progressive. On commence par découvrir le principe en utilisant exclusivement l'interface graphique avant d'aborder des cas plus complexes et le langage de requête SQL sous-jacent.

Commentez

- Titre : Les requêtes avec regroupement

- Auteur : f-leb

- Parution : 12 décembre 2012

- Mise à jour : 8 janvier 2013

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2012 f-leb. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

Sommaire

- I. Introduction

- II. Prérequis

- III. Principe

- IV. Les opérations d'agrégation

- IV-A. Opération Compte

- IV-A-1. Compte([NomDuChamp])

- IV-A-2. Compte(*)

- IV-A-3. Compte([idCommande]) ou Compte(*) ?

- IV-B. Opération Somme

- IV-B-1. Principe

- IV-B-2. ...mais Access ne sait pas faire des additions !

- V. Les filtres

- V-A. Opération « Où »

- V-B. Filtre sur opération d'agrégation

- V-C. En résumé

- VI. Regroupement sur plusieurs champs

- VI-A. Exemple avec deux champs

- VI-B. Notion de dépendance fonctionnelle

- VI-C. Optimisation

- VI-C-1. Utilisation de l'opérateur d'agrégation « Premier »

- VI-C-2. Utilisation d'une sous-requête

- VII. Vous avez essayé d'exécuter une requête...

- VIII. Et le code SQL ?

- IX. « Best of » de requêtes avec regroupement

- IX-A. Comptage sur une sous-requête qui isole des enregistrements distincts

- IX-B. Repérer des doublons

- IX-C. Somme ou Compte

- IX-D. Calculer un rang par catégorie

- X. Références pour aller plus loin

- XI. Conclusion

- XII. Remerciements

I - Introduction

À quelles occasions a-t-on besoin d'opérer des regroupements dans une requête Access ?

(Opération de regroupement accessible lors du clic sur le bouton dans l'interface QBE(1), ou directement en langage SQL grâce à la clause de regroupement GROUP BY).

Typiquement, lorsque vous souhaitez le total des commandes par client, vous devez dans un premier temps opérer des regroupements de l'ensemble de chacune de ses commandes.

Dans un deuxième temps et pour chaque regroupement de client obtenu, vous additionnez les montants de ses commandes.

En d'autres termes, il s'agit de partitionner un ensemble d'enregistrements en plusieurs groupes disjoints afin d'effectuer une opération de synthèse sur chacun des groupes (un comptage, une somme, une moyenne, etc.).

Une table est comparable à un sac où seraient mélangés les torchons et les serviettes. Si on veut compter les nombres de torchons et de serviettes, il faut regrouper et faire deux tas.

II - Prérequis

Finalement, ils sont peu nombreux. On vous épargne même les rudiments du fameux langage d'interrogation SQL(2).

Tout juste on vous propose d'avoir affûté vos premières armes grâce au tutoriel de jeannot45 : Créer des requêtes simples.

III - Principe

On part d'une table TblCommande (inspirée du cas d'école de la base « Les Comptoirs ») dont nous donnons un aperçu partiel :

Commençons par analyser une requête de sélection toute simple et le résultat trié retourné :

Ici, nous demandons pour chacun des 830 enregistrements (et donc pour les 830 commandes passées) de la table, d'afficher le champ [CodeClient](3).

En particulier, les quatre lignes avec le [CodeClient]= « ALFKI » montrent que celui-ci a passé quatre commandes, le [CodeClient]= « ANATR » quatre commandes, [CodeClient]= « ANTON » sept commandes, etc.

Retournons dans le QBE et cliquons sur le bouton (malheureusement nommé) « Totaux » qui prend l'aspect :

Dans la fenêtre d'édition, la ligne supplémentaire « Opération » apparaît avec le libellé « Regroupement » sous le champ [CodeClient] de la table [TblCommande] :

Le fil d'exécution de la requête est le suivant :

- parmi les enregistrements de [TblCommande], regrouper en sous-divisions par [CodeClient] (Opération : Regroupement. Champ: CodeClient) ;

- pour chaque sous-division obtenue, faire une synthèse en affichant le contenu du champ [CodeClient] (case « Afficher »  cochée).

Bien entendu, la requête précédente présente peu d'intérêt en pratique (elle fait son travail en se contentant de ramener les [CodeClient] sans doublon), mais elle peut le devenir si nous rajoutons une ou plusieurs opérations statistiques sur les regroupements d'enregistrements.

Imaginons que nous souhaitions compter le nombre de commandes, ceci par client.

Récapitulons à nouveau le fil d'exécution :

- parmi les enregistrements de [TblCommande], regrouper en sous-divisions les enregistrements ayant le même [CodeClient] ;

- pour chaque sous-division, compter le nombre de commandes ;

- puis pour chaque sous-division, faire une synthèse en retournant un enregistrement qui comportera les champs [CodeClient], [CompteDeIdCommande] (le comptage des commandes de la sous-division).

Dans le QBE d'Access, nous traduisons cela avec l'opération Compte :

Voilà pour le principe.

IV - Les opérations d'agrégation

Agrégation n. f. Assemblage de parties homogènes formant un tout.

Une opération d'agrégation consiste à faire une synthèse d'une série de valeurs.

Le langage SQL d'Access propose les neuf opérations d'agrégation suivantes :

Dans le QBE d'Access (version française)

Mot-clé SQL

Définition

Count

Calcule le nombre d'enregistrements renvoyés par une requête.

Sum

Renvoie la somme d'une série de valeurs contenues dans un champ de table/requête.

Avg

Calcule la moyenne arithmétique d'une série de valeurs contenues dans un champ de table/requête.

Min

Renvoie la valeur minimale d'une série de valeurs contenues dans un champ de table/requête.

Max

Renvoie la valeur maximale d'une série de valeurs contenues dans un champ de table/requête.

First

Renvoie la valeur d'un champ du premier enregistrement parmi les enregistrements retournés par la requête.

Last

Renvoie la valeur d'un champ du dernier enregistrement parmi les enregistrements retournés par la requête.

StDev

Renvoie l'estimation d'écart-type pour un échantillon de population représenté par une série de valeurs contenues dans un champ de table/requête.

Var

Renvoie l'estimation de variance pour un échantillon de population représenté par une série de valeurs contenues dans un champ de table/requête.

Dans cet article, nous nous limiterons aux deux premières opérations. L'opération Premier sera évoquée dans un cas très particulier avec les précautions qui s'imposent.

IV-A - Opération Compte

IV-A-1 - Compte([NomDuChamp])

Nous avons déjà eu un aperçu de cette opération au chapitre précédent lors du comptage des commandes par client.

La ligne « Champ » au-dessus de l'opération Compte, peut comporter un champ de table ou une expression calculée renvoyant une valeur de type numérique, texte ou date.

L'important est que dans ce cas, seuls les enregistrements où la valeur du champ est non vide (non marqué à Null), sont dénombrés.

Dans l'exemple ci-dessous, nous mettons le champ [DateEnvoi] comme paramètre de la fonction Compte:

et nous constatons sans surprise que seulement six des sept commandes du client « WOLZA » ont été dénombrées à cause de la [DateEnvoi] marquée à Null lors de sa dernière commande.

Nous ne discuterons pas ici du bien-fondé ou non de ce champ « Nullable » pour modéliser le fait, par exemple, qu'une commande n'ait pas été envoyée. Il s'agit plutôt ici de constater les premiers effets de bord dus à la présence du marqueur Null sur l'évaluation des fonctions d'agrégation.
Nous n'avons pas fini d'en discuter.

IV-A-2 - Compte(*)

Pour dénombrer tous les enregistrements, nous pouvons utiliser le caractère générique astérisque « * » comme argument de la fonction Compte. Pour cela, le QBE d'Access nous oblige à basculer la ligne « Opération » à Expression avant de saisir au clavier l'expression avec Compte(*) sur la ligne « Champ ».

NbrCommandes est l'alias(4) du champ retourné par la requête.

IV-A-3 - Compte([idCommande]) ou Compte(*) ?

Mais alors, on prend laquelle ?

Si vous utilisez un astérisque, la fonction Compte(*) calcule le nombre total des enregistrements par client même si ceux-ci ont tous leurs champs marqués à Null. Si vous utilisez Compte([idCommande]), la fonction calcule le nombre d'enregistrements par client avec [idCommande] non Null.

La clé primaire [idCommande] de TblCommande étant non Null par principe, les deux requêtes retournent rigoureusement le même résultat.

Seulement, la documentation Access précise bien :

Count(*) s'éxecute beaucoup plus rapidement que Count([nom_de_colonne]).

Donc privilégiez l'utilisation de Compte(*) lorsque cela vous est possible.

Pour distinguer l'usage de Compte(*) et Compte([NomDuChamp]), on propose l'exercice qui suit.

On souhaite le nombre de commandes par client, ceci pour tous les clients enregistrés dans la table TblClient, y compris ceux qui, à défaut d'avoir passé une seule commande, ne manifestent aucune présence dans la table TblCommande.

On commence par une requête de sélection sur les deux tables TblClient et TblCommande avec une jointure de type « 2 » pour ramener tous les clients, y compris ceux qui ne figurent pas dans la table des commandes.

Le résultat :

Tiens ! Le client « FISSA » n'a passé aucune commande. Pour obtenir le nombre de commandes par client (et donc zéro commande pour le client « FISSA »), doit-on utiliser Compte(*) ou Compte([idCommande]) ?

Réponse :

Un Compte(*) retournera un nombre de commandes égal à un pour le client « FISSA » puisqu'il y a un enregistrement retourné.

La bonne réponse est d'effectuer un Compte([idCommande]) qui ne dénombre que les enregistrements où [idCommande] est non Null.

IV-B - Opération Somme

IV-B-1 - Principe

On regarde maintenant du côté des lignes de commande :

Pour la partie facturation, nous avons besoin de regrouper les lignes de commande par [idCommande] et de faire la somme :

Somme([PrixUnitaire]*[QtteCommande])

Le principe de réalisation d'une telle requête n'appelle pas de commentaire particulier :

- opération de regroupement par [idCommande] ;

- opération d'agrégation Somme sur l'expression [PrixUnitaire]*[QtteCommande];

- la synthèse qui, par commande, retourne un enregistrement ([idCommande], [PrixCommande]).

Et on retrouve bien, par exemple pour [idCommande]=10248, la somme des lignes :

2 200 € = (70 € x 12) + (49 € x 10) + (174 € x 5)

IV-B-2 - ...mais Access ne sait pas faire des additions !

On rajoute un champ [Remise] dans le détail des commandes :

Le grain de sable a été introduit dans la commande identifiée 10248 avec un champ [Remise] négligemment marqué à Null (encadré en rouge) :

Voici ce que retourne une simple requête de sélection avec les champs calculés [AvantRemise], [MontantRemise] et [ApresRemise] :

Faisons le bilan :

Le total avant remise est de 2 200 €, le montant de la remise est de 87 €.

Le montant de la commande devrait être de 2 200 € - 87 € = 2 113 €, du moins si vous le calculiez comme cela en faisant la différence entre le montant avant la remise et la remise. En fait, tout se passe comme si le champ à Null était à zéro.

Parce que si vous faites autrement en faisant la somme des montants de la colonne [ApresRemise]…

… la facture passe à 1 273 €, amputée de la totalité de la ligne de commande à cause de cette remise non renseignée.

Votre comptable sera ravi d'apprendre que :

Somme([QtteCommande] x [PrixUnitaire]) - Somme([Remise] x [QtteCommande] x [PrixUnitaire])

ne donne pas toujours le même résultat que :

Somme([QtteCommande] x [PrixUnitaire] x (1 - [Remise]))

Access ne sait pas faire des additions

Vous pouvez toujours rétorquer que le développeur averti aurait naturellement surchargé sa programmation à coup de :

Nz([remise];0)

qui remplace les marqueurs Null par la valeur numérique 0.

(Imaginez toutefois la moyenne sur le bulletin du gamin si en suivant le même conseil, le logiciel remplaçait les notes non renseignées d'un devoir par zéro !)

Si le cahier des charges de l'application précise que la remise doit être systématiquement renseignée, la solution idéale est d'interdire tout simplement la présence de l'affreux Null dans la table :

Quoi qu'il en soit, on vous a maintenant assez prévenu des effets parfois néfastes du marqueur Null.

V - Les filtres

V-A - Opération « Où »

On revient sur une requête précédente :

L'opération « Où » dans une requête avec regroupement effectue une restriction sur les enregistrements avant que le regroupement ne soit effectif.

Ici, le regroupement est donc opéré uniquement sur les enregistrements vérifiant le critère :

Année([DateCommande])=Année(Date())

Trois commandes en 2012 pour le client [ALFKI], une seule en 2012 pour [ANATR], etc.

Essayons maintenant les deux requêtes suivantes visant à compter, parmi les commandes passées lors de l'année en cours, le nombre de commandes pour les [CodeClient] commençant par la lettre « A ».

Dans la première version, la requête effectue une restriction supplémentaire sur le [CodeClient]. Les enregistrements de la table des commandes dont l'année de la [dateCommande] ne correspond pas à l'année en cours et dont le [CodeClient] ne commence pas par « A » sont exclus avant d'opérer le regroupement et de retourner la synthèse :

Dans la deuxième version, on effectue comme précédemment une restriction sur la [dateCommande]. La différence est qu'ici on opère d'abord le regroupement et le comptage. Puis seulement après, on retire les synthèses dont le [CodeClient] ne commence pas par « A » :

Il est évident que dans ces versions, les deux requêtes retournent un résultat identique. Toutefois la deuxième version est déconseillée puisqu'elle oblige d'abord à opérer le comptage sur 83 sous-divisions avant finalement de n'en retenir que quatre.

V-B - Filtre sur opération d'agrégation

Par contre, si on souhaite faire une restriction sur le nombre de commandes passées, on doit filtrer sur le résultat retourné par la fonction d'agrégation Compte.

Ainsi, si nous voulons une requête qui, pour les commandes passées lors de l'année en cours, ne retienne que les clients ayant commandé plus de cinq fois, on doit :

- retirer les enregistrements de TblCommande dont l'année de la [dateCommande] ne correspond pas à l'année en cours (opération « Où ») ;

- opérer un regroupement sur le [codeClient] (opération « Regroupement») ;

- compter tous les enregistrements pour chaque sous-division opérée (opération Compte) ;

- exclure les synthèses où le comptage donne un résultat inférieur ou égal à 5 (critère « >5 ») ;

- afficher la synthèse retournée (cases « Afficher » cochées).

Ce qui donne dans le QBE :

V-C - En résumé

L'opération « Où » permet de filtrer les enregistrements avant que le regroupement ne soit effectif.

Un critère sur une opération d'agrégation n'agit qu'après regroupement et synthèse. On l'utilise pour éliminer les synthèses ne vérifiant pas le critère sur l'expression agrégée (critère sur un comptage, une somme, etc.).

VI - Regroupement sur plusieurs champs

VI-A - Exemple avec deux champs

À des fins comptables ou statistiques, nous pouvons être amenés à déterminer les nombres de commandes passées par nos clients, et ceci par année civile.

Le partitionnement des enregistrements de la table des commandes doit se faire de la façon suivante, par client et par année de la date de commande :

Dans le QBE, la solution vient naturellement en regroupant dans la table des commandes par commande et par année (champ calculé : Année([DateCommande]).

Dans ce cas, le couple ( [CodeClient], Année([DateCommande]) ) constitue ce qu'on appelle souvent la clé de regroupement.

VI-B - Notion de dépendance fonctionnelle

Afin de compléter la requête précédente, nous voulons, en plus du [CodeClient], le nom du pays où siège la société [Pays].

Rechercher le champ [Pays] nécessite de faire participer également la table des clients dans la requête :

Il se trouve que [Pays] est « fonctionnellement dépendant » de [CodeClient].

L'objet de cet article n'étant pas le Modèle Relationnel de Données et la normalisation des tables des SGBD relationnels, nous resterons très simple au niveau du concept de dépendance fonctionnelle (DF). Si le lecteur souhaite se familiariser plus en détail, il se tournera vers l'article correspondant : Bases de données relationnelles et normalisation : de la première à la sixième forme normale, notamment au paragraphe Dépendance fonctionnelle (DF).
Si nous reprenons la table des clients, un client a un [CodeClient] unique et chaque client est en fait une société avec un nom de société [Societe].
Nous pouvons écrire l'instruction :
[CodeClient]→[Societe]
parce que pour un [CodeClient] donné, correspond exactement un nom de [Societe].
On dit alors que [Societe] est fonctionnellement dépendant de [CodeClient] ou que [CodeClient] détermine fonctionnellement [Societe].
[CodeClient] étant la clé primaire de la table, il définit fonctionnellement chaque attribut de la table des clients :
[CodeClient]→[Societe]
[CodeClient]→[Contact]
…
[CodeClient]→[Adresse]
…
[CodeClient]→[Pays]
Note : D'ailleurs, si vous avez pris le coup, vous vous rendrez compte qu'il serait naturel d'écrire aussi la dépendance :
[Contact]→[Fonction]
(Si j'identifie un contact donné, j'ai bien déterminé sa fonction, non ?)
Ne faudrait-il pas une table TblContact ? Bravo, vous venez de découvrir une des raisons pour laquelle la base de données « Les comptoirs » n'est vraiment pas un modèle en termes de normalisation. Passons…

Voyons ce que donne le partitionnement de la table des commandes par [CodeClient] et par Année([DateCommande]) si nous ajoutons simplement le champ [Pays] pour chaque enregistrement :

Nous constatons que grâce à la dépendance fonctionnelle [CodeClient]→[Pays], le partitionnement demeure inchangé si nous rajoutons [Pays] dans la clé de regroupement :

Comme nous avons aussi les dépendances fonctionnelles :

[CodeClient]→[Societe]

[CodeClient]→[Contact]

…

[CodeClient]→[Adresse]

nous pouvons généraliser dans le QBE et obtenir toutes les caractéristiques du client dans notre requête de comptage :

Cette méthode n'est toutefois pas très recommandée en termes de performance comme nous allons le voir dans le paragraphe suivant.

VI-C - Optimisation

On pourrait dire beaucoup de choses sur l'optimisation des requêtes (cf. le Guide d'optimisation de vos applications Microsoft Access). Les conseils de bon sens comme celui de regrouper sur un nombre de champs minimal de type entier numérique et indexés (à peu près tout le contraire de la requête précédente) s'avèrent judicieux « en principe », surtout si les tables deviennent volumineuses.
Toutefois il y a de nombreux facteurs et un prototypage en règle de vos requêtes sensibles dans l'environnement de production est le meilleur moyen de conclure de l'avantage d'une requête sur telle autre.

On reprend la requête peu recommandable précédente. On rappelle que cette requête est censée ramener le nombre de commandes par client et par année.

Ramener les informations du client dans la clé de regroupement pour obtenir le résultat souhaité est certes possible grâce aux dépendances fonctionnelles :

[CodeClient]→[Pays]

[CodeClient]→[Societe]

[CodeClient]→[Contact]

[CodeClient]→[DateCommande]

mais il reste qu'une clé de regroupement sur un nombre élevé de champs est coûteuse en termes de performance. Deux méthodes permettent d'obtenir le même résultat de façon optimisée.

VI-C-1 - Utilisation de l'opérateur d'agrégation « Premier »

Après tout, le partitionnement sur les seuls [CodeClient] et Année([DateCommande]) devraient suffire. Si on veut ramener les autres attributs du client, il suffit pour chaque sous-division de prendre les informations sur une seule des lignes de la sous-division. Pourquoi pas la première en utilisant l'opérateur d'agrégation Premier ?

Dans une table de base de données relationnelle, sélectionner le « premier » enregistrement n'a pas de sens. Les lignes de la table sont comparables aux billes d'un sac de billes. Que signifie la « première » bille ? Suivant quel critère ? D'ailleurs l'opérateur d'agrégation Premier (mot-clé : First) est propre à Access, vous ne le trouverez nulle part dans la norme SQL.
Malgré l'aberration que cet opérateur représente et le caractère aléatoire de ce qu'Access retourne lorsqu'on lui demande de retourner le « premier » enregistrement, dans notre contexte où toutes les billes regroupées sont identiques, il est tolérable de prendre la « première » trouvée (parce qu'ici, on aurait aussi bien pu lui demander la « dernière » - mot-clé : Last).

VI-C-2 - Utilisation d'une sous-requête

L'idée est encore d'optimiser la clé de regroupement. On regroupe sur un minimum de champs, de préférence de type numérique et indexés. Le [CodeClient] et l'année de la date de commande Année([DateCommande]) feront l'affaire ici.

La requête principale reprend la sous-requête :

C'est par jointure de la table des clients et la sous-requête via le [CodeClient] que nous récupérons les attributs du client dans une requête simple de sélection :

Bien qu'elle semble « coûter » davantage à cause de la sous-requête, le fait d'optimiser la clé de regroupement améliore en principe les performances malgré l'opération de jointure supplémentaire (qui est quand même une opération sensible et en principe ultraoptimisée dans un SGBD relationnel).

VII - Vous avez essayé d'exécuter une requête...

…ne comprenant pas l'expression spécifiée « Year([DateCommande]) » comme une partie de la fonction d'agrégat.

C'était pourtant bien tenté, merci d'avoir essayé !

Voici la requête fautive, forcément produite par un clavier qui a fourché !

Le champ calculé [AnneeCommande] a été accidentellement retiré de la clé de regroupement. Dès lors, [AnneeCommande] n'est plus « agrégé ».

Au départ vous vouliez regrouper par client et par année de la commande mais par un clic malencontreux vous avez sorti le champ calculé Année([DateCommande]) de la clé de regroupement. Dans ce cas, non seulement il ne fait plus partie de la clé de regroupement, mais en plus il ne fait l'objet d'aucune opération d'agrégation.

On reprend l'extrait du partitionnement engendré par le regroupement sur le seul [CodeClient] demandé mais en faisant apparaître le champ calculé Année([DateCommande]):

« ALFKI » a passé une commande en 2011 et trois en 2012, etc.

Puis on récapitule le fil des différentes étapes d'exécution de la requête :

- parmi les enregistrements de [TblCommande], regrouper en sous-divisions les enregistrements ayant le même [CodeClient] ;

- pour chaque sous-division, compter le nombre de commandes ;

- puis pour chaque sous-division, faire une synthèse en retournant une ligne qui comportera les champs [CodeClient], [NbCommandes] et Année([DateCommande]) (cases « afficher » cochées).

C'est dans cette dernière étape que le bât blesse. En demandant l'affichage de l'année de commande dans chaque sous-division alors qu'il peut y en avoir plusieurs par sous-division (ici les années 2011 et 2012), Access est incapable d'en faire une synthèse dans une seule ligne.

Dans la sous-division « WOLZA », impossible de faire une synthèse d'une ligne sur l'année. Quelle année prendre, sur quelle opération d'agrégation ? 2011, 2012 ? 2011,5 ???

D'où le message d'erreur.

De là, on peut en tirer une règle générale sous Access :

Tout champ non agrégé dans la liste des champs affichés (avec la case « Afficher » cochée) doit figurer dans la clé de regroupement (opération : « regroupement »).
Autrement dit, les requêtes avec regroupement peuvent afficher un champ uniquement si ce champ fait partie de la clé de regroupement, ou s'il apparaît dans une fonction d'agrégation.

VIII - Et le code SQL ?

Vous avez sans doute déjà basculé une requête en « Mode SQL ». Pour voir…

Pour les commandes avec livraison en France, afficher les clients et leur nombre de commandes si ceux-ci ont passé plus de cinq commandes

Ainsi, pour une requête de regroupement en langage SQL, vous trouverez une structure du genre :

1. SELECT listechamps

2. FROM Table

3. WHERE Condition1

4. GROUP BY CléRegroupement

5. HAVING Condition2

6. ORDER BY ListeChamps2;

La première ligne SELECT fait référence aux champs avec la case « Afficher » cochée dans le QBE. Vous y trouverez les opérations d'agrégations comme Count (pour Compte), Sum (pour Somme), First (pour Premier), etc.

La deuxième ligne avec FROM fait référence aux tables/requêtes participant à la requête avec les jointures INNER JOIN, LEFT JOIN ou RIGHT JOIN comme dessinées dans le QBE.

La troisième ligne WHERE permet d'opérer une restriction au sein des enregistrements. On rappelle que la restriction est effectuée avant que le regroupement ne soit effectif. Dans le QBE, la restriction sur un champ s'opère avec l'opération « Où » avec la condition de restriction à la ligne « critère » située en dessous.

À la suite de la clause GROUP BY de la quatrième ligne, on trouve les différents champs de la clé de regroupement séparés par une virgule.

Dans la cinquième ligne avec HAVING, on opère des restrictions sur des fonctions d'agrégation, du style :

HAVING Count(*)>5

pour restreindre aux sous-divisions donnant un comptage supérieur à 5.

La dernière ligne avec ORDER BY permet d'opérer des tris dans le résultat retourné. Elle apparaît lorsque dans le QBE vous complétez les lignes avec « Tri » à « Croissant » ou « Décroissant ».

Le langage SQL est évidemment très riche (celui d'Access sans doute moins…) mais faire la bascule de temps à autre entre le mode graphique et le mode SQL vous permettra d'aborder plus facilement la syntaxe de ce langage. Et puis quand viendra le temps de vous jeter à l'eau, vous serez à même d'exploiter vos requêtes SQL par copier-coller dans vos programmes en langage VBA (cf. le tutoriel : Modifier une instruction SQL (ou requête), en Visual Basic).

Vous pourriez alors nager en eaux plus profondes et même sortir des requêtes abyssales (l'affaire d'un autre article peut-être), non interprétables graphiquement dans le QBE, mêlant sous-requêtes imbriquées, corrélées et autres ressources qu'offre le langage SQL.

IX - « Best of » de requêtes avec regroupement

Ici je propose un recueil de requêtes avec Somme et Compte et qui reviennent régulièrement dans nos forums. À l'heure où j'écris ces lignes, certaines de ces requêtes avec regroupement et qui mériteraient d'apparaître dans ce « Best of » ont pu m'échapper. N'hésitez pas à me contacter par MP si le cas se présente.

IX-A - Comptage sur une sous-requête qui isole des enregistrements distincts

Si vous souhaitez dénombrer les clients ayant commandé par année civile, la requête qui suit vous retournera un résultat faux :

Ceci parce qu'un même client ayant commandé, par exemple, trois fois dans l'année sera compté trois fois. Ce que vous vouliez en fait, c'est le nombre de clients distincts ayant commandé dans l'année.

Le client « ALFKI » a passé trois commandes en 2012, ce client sera compté trois fois !

Pour cela, Access vous oblige à passer par une sous-requête intermédiaire ne listant qu'une seule fois les clients ayant passé commande par année civile (en supprimant les doublons sur le couple ([CodeClient], [AnneeCommande]) :

On dénombre ensuite les clients en interrogeant la sous-requête :

IX-B - Repérer des doublons

Un classique du genre…La plaie des bases de données, les doublons.

Si la table des employés comporte deux fois les mêmes nom et prénom, il peut s'agir d'un homonyme mais il est aussi probable que ce soit la même personne saisie deux fois.

Ce genre de doublons n'est pas difficile à repérer, il suffit de mettre les champs [Nom] et [Prénom] dans la clé de regroupement et retourner les sous-divisions dénombrant au moins deux enregistrements. Par exemple :

Si vous voulez voir comment Access se débrouille avec les doublons, inspirez-vous des requêtes proposées par l'assistant :

IX-C - Somme ou Compte

La table TblProduit comporte un champ booléen (de type « Oui/Non ») nommé [Indisponible] :

Si on souhaite dénombrer les produits indisponibles par catégorie de produit, voici ce que l'on pourrait composer avec le QBE d'Access :

On restreint les enregistrements aux seuls produits ayant le marqueur [Indisponible] à Vrai avant d'effectuer le regroupement puis le comptage.

Sous Access, quel que soit le format d'affichage du champ booléen « Oui/Non », « Vrai/Faux » ou « Actif/Inactif », celui-ci est stocké sous une forme numérique avec :

- [Inactif]=-1 si « Vrai », « Oui » ou « Actif »

- [Inactif]=0 si « Faux », « Non » ou « Inactif »

Vous pouvez tester la requête suivante en utilisant la fonction de conversion en entier CEnt pour vous en convaincre :

Dès lors, nous pouvons aussi obtenir notre comptage en sommant les valeurs numériques sous-jacentes au champ booléen (au signe près), ce qui donne une autre version du même comptage :

Notez le signe « - » devant l'opération Somme.

Si cette astuce semble simplifier la requête dans son écriture, il vous appartient de vérifier sa pertinence du point de vue des performances sur des tables volumineuses.

Mais imaginons que l'on souhaite en plus connaître le nombre d'alertes de réapprovisionnement par catégorie de produit. Les alertes doivent être déclenchées à partir du moment où [UnitesEnStock] devient inférieur ou égal à [NiveauReappro].

On pourrait calculer un « booléen » qui vaudrait 1 si [UnitesEnStock]<=[NiveauReappro] et 0 sinon avec l'instruction VraiFaux :

VraiFaux([UnitesEnStock]<=[NiveauReappro];1;0)

On se ramène alors au cas précédent avec le champ [Indisponible]. Il ne reste plus qu'à faire la somme :

Et voilà comment avec deux opérations « Somme » obtenir deux comptages « conditionnels » suivant la disponibilité et le niveau du stock.

IX-D - Calculer un rang par catégorie

Cette fois nous partons d'une table (temporaire) TblCA2012 avec le chiffre d'affaires 2012 pour chaque produit :

L'objectif est d'obtenir les cinq meilleurs CA avec le rang, ceci par catégorie de produit :

Il y a plusieurs façons de résoudre le problème, comme celle inspirée des sources Access :

Classer les résultats d'une requête

Si toutefois vous êtes fâché avec le méchant SQL, la solution présentée qui suit (entièrement réalisée dans l'interface QBE) oblige à passer par une sous-requête intermédiaire :

Celle-ci, pour chaque produit, va dénombrer les produits (champ calculé [NbProd]) de la même catégorie (grâce à la jointure avec TblCA2012_1) qui ont un chiffre d'affaires supérieur (grâce au critère de l'opération « Où »).

D'après le résultat intermédiaire ci-dessus, le produit identifié 1 sera au cinquième rang de sa catégorie en termes de chiffre d'affaires puisque la sous-requête ramène quatre produits de la même catégorie ayant un chiffre d'affaires strictement supérieur.

Le premier étage de la fusée pour le calcul du rang est prêt. Vous noterez la jointure de la table TblCA2012 avec elle-même (représentée via l'alias TblCA2012_1, obtenu en ajoutant une deuxième fois TblCA2012 dans la fenêtre).

Il reste à conclure avec la requête principale de sélection :

Le rang d'un produit en termes de CA est le nombre de produits de la même catégorie ayant un CA strictement supérieur, auquel on rajoute 1.

Les meilleurs produits (avec le rang égal à 1) ne sont pas retournés par la sous-requête puisqu'aucun produit de la même catégorie n'a de CA supérieur.

D'où la nécessité d'une jointure de type « 2 » entre TblCA2012 et SousReq_CA, en plus de la fameuse fonction Nz dans le calcul du rang.

Nz([NomDuChamp]; Valeur si Null)

X - Références pour aller plus loin

- Quelques définitions en usage dans l'univers des SGBDR et du SQL

- Le SQL de A à Z : 4e partie - groupage et ensembles

- Démythifier le GROUP BY

- Guide d'optimisation de vos applications Microsoft Access (III-E. Requêtes)

XI - Conclusion

Vous voilà armés pour composer vos requêtes de regroupement en déjouant les pièges (le cas des champs à Null). Vous n'aurez même aucun mal à étendre vos connaissances aux opérateurs d'agrégation passés sous silence dans ce tutoriel (comme les classiques « Moyenne », « Max » et « Min »).

XII - Remerciements

Je remercie Pierre Fauconnier et CinePhil pour leur précieuse aide technique.

Je remercie également l'ami ClaudeLeloup pour sa relecture orthographique.

(1)QBE : sigle pour « Query By Example », l'interface graphique pour travailler les requêtes et générer le code SQL correspondant.

(2)SQL : sigle pour « Structured Query Language », langage normalisé de création et de manipulation des données de la plupart des Systèmes de Gestion de Bases de Données Relationnelles (SGBDR).

(3)L'usage dans l'univers des SGBDR et le langage SQL va dans le sens de l'utilisation des termes Colonne et Ligne en remplacement de Champ et Enregistrement (« les champs sont à la campagne ou dans les formulaires » comme aime à le rappeler CinePhil). Les termes Ligne et Colonne sont d'ailleurs appropriés à la représentation tabulaire des tables en mode « feuille de données » mais Microsoft en a décidé autrement pour Access. En conséquence, nous continuerons d'utiliser Champ puis Enregistrement dans cet article, conformément aux usages dans l'univers Access.

(4)Alias : un nom de substitution donné à un champ d'une table/requête.