Maitriser les requêtes 04

LES REQUETES AJOUT ET ANALYSE CROISEE

1 - Introduction    

La requête de sélection ne peut pas tout faire, et sous la pression des besoins, d'autres types de requête ont été créés. Nous en avons rassemblé deux dans ce chapitre (l'ajout et l'analyse croisée), et deux dans le chapitre suivant (la suppression et la mise à jour). 

La requête analyse croisée est une spécificité d'Access, et on ne la retrouve généralement pas dans les autres SGBD. 

Pour les afficionados du SQL, la requête ajout n'est pas connue sous ce nom ; elle est simplement considérée comme un cas particulier d'utilisation de la commande INSERT.    

Il reste une grande absente, la requête union, qu'on ne peut pas créer dans la fenêtre graphique d'Access, mais que nous traiterons lorsque nous étudierons le langage SQL.      

2 - Le fonctionnement de la requête ajout    

La requête ajout permet d'insérer les enregistrements d'une table n° 2 dans une table n° 1. L'opération ne peut se faire que si les deux tables ont au moins un champ commun (même nom, même type de données ou conversion de type possible -- cela dépend du SGBD). 
Comme le montre la figure ci-dessous, les champs de la table n° 2 qui ne sont pas communs avec ceux de la table n° 1 sont ignorés ou refusés (ex : le champ "T"). 
Les champs de la table n° 1 qui n'existent pas dans la table n° 2 ne sont pas renseignés (ex : le champ "W") -- à moins que le champ ne soit du type NuméroAuto, auquel cas le système le remplira lui-même, comme nous le constaterons dans un prochain exemple.

Attention ! la requête ajout modifie irréversiblement la table à laquelle on ajoute des données (la table n° 1 dans la terminologie du paragraphe ci-dessus). 
L'opération une fois effectuée, il n'est plus possible de revenir en arrière. 
Il est donc très fortement recommandé de créer une copie de la table n° 1 avant de procéder à l'ajout. 
La table que l'on ajoute (la table n° 2) n'est ni modifiée, ni supprimée, au cours de l'opération.

1. Pour créer une requête ajout dans le SGBD Access, nous introduisons la table à ajouter (la table n° 2 dans notre terminologie) dans la fenêtre de création/modification d'une requête, et nous sélectionnons les champs que nous voulons -- ou que nous pouvons -- ajouter. 
2. Puis nous cliquons sur la petite flèche qui borde l'icône "Type de requête" et, dans la liste déroulante qui s'affiche, nous choisissons "Requête Ajout...". 
3. Dans la boite de dialogue "Ajout" qui s'ouvre, nous précisons quelle est la table dans laquelle doit s'effectuer l'ajout (la table n° 1 dans notre terminologie). 
4. La grille de définition de la requête acquiert alors une ligne supplémentaire intitulée "Ajouter à :", comme le montre la figure ci-dessous.

Les données de la table n° 2 seront effectivement ajoutées à la table n° 1 lorsque nous exécuterons la requête. 
Des messages nous avertiront de ce qui se passera -- à moins que nous n'en ayons décidé autrement dans les options (onglet "Modifier/Rechercher", cadre "Confirmer" sous Access 2003).    

Diverses sophistications sont possibles. Nous pouvons :    

• sélectionner une partie seulement des champs de la table n° 2 ;
• sélectionner à l'aide de critères les enregistrements de la table n° 2 qui doivent être ajoutés à la table n° 1 ;
• remplacer la table n° 2 par une requête ;
• faire en sorte qu'une requête (mono ou multi-table) effectue également un ajout dans une autre table.

   

Il nous faut cependant bien veiller à ce que les colonnes qui sont utilisées pour définir les opérations de sélection, mais qui ne sont pas concernées par l'ajout, ne contiennent aucune information sur la ligne "Ajouter à :", sinon le SGBD Access nous gratifiera d'un message d'erreur qui nous plongera dans des abîmes de réflexion (exemple à méditer : "Destination de sortie 'requête' répliquée").    

Voici une liste non limitative des diverses utilisations de la requête ajout :    

• rassembler dans une même table des enregistrements provenant de tables séparées. Dans cette application, la requête ajout entre en concurrence avec la requête union, que nous étudierons dans l'un des chapitres consacrés au SQL. 
• Attention : les deux requêtes n'imposent pas les mêmes contraintes, et ne fournissent pas forcément le même résultat (problème des doublons) ;
• imposer à une table des propriétés particulières, en l'ajoutant à une table modèle,  initialement vide et dont les propriétés sont soigneusement définies (largeur et visibilité des colonnes, tri, police de caractères, etc.) ;
• garder trace d'un classement dans une table ;
• etc.

Dans le paragraphe suivant, nous examinerons quelques exemples d'utilisation de la requête ajout.

3 - L'utilisation de la requête ajout (exemples)

Notre premier exemple illustre simplement la procédure exposée ci-dessus. La figure suivante représente le contenu des deux tables avant et après l'ajout.

Attention ! Le résultat de l'ajout dépend de l'ordre dans lequel on effectue les opérations. 

Si nous permutons les rôles des tables 1 et 2, nous redéfinissons notre requête ajout comme suit :

SGBD Access n'acceptera pas que nous tentions d'introduire dans une table des champs qui n'y sont pas initialement présents. 
Nous notons que seuls les noms des champs de la table n° 2 figurent dans la grille, mais nous pourrions écrire "Table2.Nom" et "Table2.Prénom" à la place "Nom" et de "Prénom", sans que le système ne proteste.

La figure ci-dessous représente le contenu des deux tables avant et après l'ajout.

Notre second exemple, directement inspiré de la fin du précédent, illustre l'introduction de critères aux enregistrements de la table que l'on ajoute à l'autre. 
Voici comment se présente la grille de la requête, si l'on impose des critères à deux champs de la table n° 1 avant de l'ajouter à la table n° 2 (pour faire bonne mesure nous avons également ajouté un tri) :

 Et voici le résultat :

 

Notre troisième exemple montre comment on peut modifier les propriétés d'une table en l'ajoutant à une table vide. 
La table n° 1 que nous avons utilisée ci-dessus comporte un champ "Date", pour lequel nous avons choisi le format jj/mm/aaaa, appelé "Date, abrégé" en mode création. 
Nous voulons maintenant obtenir la date dans le format complet (exemple : dimanche 19 juin 1944) pour avoir connaissance du jour. 
La méthode la plus simple consiste, bien entendu, à changer manuellement de format en mode création. Mais si l'opération doit être répétée souvent, il faut trouver un moyen pour l'automatiser. 
Une requête ajout, qu'il est facile d'exécuter depuis une macro, nous fournit la solution.

Par copier/coller (structure seulement) à partir de la table n° 1, nous obtenons une table "modèle" qui contient les mêmes champs (mais vides). 
Nous modifions le format du champ "Date", initialement "Date, abrégé", en "Date, complet" et nous enregistrons la modification. 
Grâce à une macro, nous créons une copie de la table "modèle" que nous appelons table n° 3, puis nous lui ajoutons tous les champs de la table n° 1. 
Nous constatons que, dans la table n° 3, la date s'affiche en format complet, comme le montre la figure ci-dessous.

Il existe en principe une méthode beaucoup plus simple pour modifier les propriétés d'un champ de manière automatisable. 
Elle consiste à écrire une requête SQL utilisant la commande ALTER TABLE avec la clause MODIFY. Malheureusement, la clause MODIFY ne fonctionne pas dans Access... et on utilise la requête ajout pour pallier cette déficience.

Notre quatrième exemple montre comment on peut garder la trace du classement d'une table. 
Pour ce faire, nous traitons l'exemple d'une entreprise qui veut établir la liste de ses produits classés par ordre décroissant de chiffre d'affaires (CA) au cours de l'année écoulée. 
Le point de départ est une table contenant la liste des produits (classés par ordre alphabétique) avec leur CA. 
La méthode la plus simple consiste à trier la table par ordre de CA décroissant, à l'enregistrer, puis à la doter (en mode création) d'une colonne supplémentaire du type de données NuméroAuto. 
Mais si nous avons besoin d'automatiser l'opération, il nous faut recourir à une autre solution.

La figure ci-dessous montre la méthode utilisée. 

1. Nous trions la table de départ (Table1) par ordre de CA décroissant. 
2. Nous l'ajoutons à une table vide contenant les mêmes champs, plus un champ de type NuméroAuto (Table2). 
3. Puis, à l'aide d'une requête de sélection simple, nous trions la table Table2 par ordre alphabétique du premier champ. 
4. Le résultat final est une table des produits classés par ordre alphabétique, avec une colonne indiquant le rang de classement par ordre de CA décroissant. 

On notera que le champ "Classement" a été rempli par le SGBD (l'opérateur ne peut pas écrire dans ce champ).

Une autre solution à ce problème consiste à utiliser la commande ALTER TABLE en SQL, avec la clause ADD COLUMN et le type de données COUNTER, qui correspond à NuméroAuto.

4 - L'ajout sans doublons

La requête ajout crée des doublons si la seconde table contient des enregistrements identiques à ceux de la première. 
Le cas le plus flagrant résulte de l'ajout d'une table à elle-même, opération qui est tout à fait licite dans Access, même si son intérêt parait à peu près nul. On notera que la plupart des SGBD interdisent cette opération.            

1. Le premier correctif auquel nous songions consiste à basculer de "Non" à "Oui" la propriété "Valeurs distinctes" de la requête ajout. Ainsi modifiée, la requête n'élimine pas les doublons qui résultent de l'ajout, mais évite de transporter dans la première table des enregistrements qui constituent des doublons dans la seconde. C'est mieux que rien, mais ce n'est pas suffisant.   
2. Le second correctif auquel nous songions consiste à créer un index sans doublons sur les champs de la première table communs avec ceux de la seconde table. Lorsque nous lançons la requête ajout, nous recevons l'alerte suivante, qui constitue un morceau d'anthologie en matière de message informatique. Mais si nous admettons que par "violation de clé" il faut entendre "violation d'indexation sans doublons", tout s'éclaire :

Cliquons sur "Oui" et le tour est joué : le SGBD n'ajoute que les enregistrements qui ne créent pas de doublon. Si nous cliquons sur "Non", la requête est annulée. Si nous cliquons sur "Aide", nous obtenons une aide qui n'a rien à voir avec le contexte.    

Deux autres solutions peuvent être pratiquées :   

      

• faire suivre la requête Ajout d'une requête qui élimine les doublons. Nous avons appris à créer une telle requête dans le chapitre précédent ;
• créer une requête Union en langage SQL. Nous apprendrons à nous servir du langage SQL dans un chapitre ultérieur.

   

Il faut cependant bien noter que l'ajout et l'union fonctionnent de manière distincte. 
Dans la requête union, les deux tables que l'on réunit jouent des rôles identiques. Comme nous venons de le constater, ce n'est pas le cas dans la requête ajout.

5 - L'analyse croisée

La requête analyse croisée s'applique à une table comportant au moins trois colonnes, et possédant des caractéristiques particulières. 
L'une des colonnes doit comporter des doublons, sur lesquels sera effectuée l'opération deregroupement (la colonne "U" de la figure ci-dessous). 
Une autre colonne (la colonne "W" de la figure ci-dessous) doit comporter un nombre restreint de valeurs distinctes, qui serviront à créer les nouvelles colonnes. 
Un assistant facilite la création de ce type de requête, dont la conception n'est pas aisée.

Considérons l'exemple de la table (nommée "Table1") représentée ci-dessous. Une entreprise a dressé la liste de ses fournisseurs et, pour chacun d'entre eux, la liste des produits fournis ainsi que le classement par ordre de chiffre d'affaires.

Dans la fenêtre "Base de données", l'objet "Requêtes" étant sélectionné, nous cliquons sur le bouton nouveau, nous choisissons "Assistant Requête analyse croisée" dans la liste qui s'affiche, et nous cliquons sur "OK". Le dialogue suivant s'établit avec l'assistant :

1. nous indiquons d'abord sur quelle table nous voulons opérer. Dans le cas présent, il s'agit de la table "Table1" ;
2. nous choisissons le champ "Société" comme "en-tête de ligne". Dans le jargon de l'éditeur, cela signifie que ce champ sera le premier de la nouvelle table ;
3. nous choisissons le champ "Rang" comme "en-tête de colonne". Cela signifie que le SGBD va créer les colonnes "1", "2", etc. ;
4. nous choisissons "premier" et nous décochons la case "Oui, inclure les sommes des lignes" car les données sont du type texte et non du type numérique ;
5. nous cliquons sur le bouton "Terminer" et nous basculons en mode feuille de données pour examiner le résultat (figure ci-dessous). 

 

La requête analyse croisée est surtout utilisée dans le domaine financier, où elle sert à créer des bilans à partir de données comptables. 
Les nouvelles colonnes qui sont crées correspondent alors à des périodes de temps données (jours, semaines, mois, etc.).

Attention ! Si nous demandons à cette requête de créer une table, le résultat obtenu est erroné. Il y a là un bug que nous pouvons contourner en créant une requête sélection simple (avec création de table et conservation de tous les champs), opérant sur le résultat de la requête analyse croisée.

5 - Conclusion

Comme vous avez pu le constater, nous avons rassemblé dans ce chapitre deux requêtes qui n'ont pas de points communs. 
Vous voudrez bien nous en excuser... sachant que nous ferons mieux dans le chapitre suivant, où les requêtes de suppression et de mise à jour peuvent être regroupées sous la bannière unique de la maintenance des BDD.