Accueil Recherche | Plan Technique | Liens | Actualités | Formation | Emploi | Forums | Base
TUTORIEL cerig.efpg.inpg.fr 
Vous êtes ici : Accueil > Formation > Tutoriels > Bases de données relationnelles > Les requêtes ajout et analyse croisée
        Révision : 24 février 2003
 
                  Les bases de données
relationnelles
                 
Chap.
précéd.
Plan du
tutoriel
Liste des
tutoriels
Chap.
suivant
Chapitre 16 : les requêtes ajout et analyse croisée
1 - Introduction
                  Nous avons consacré 5 chapitres à la requête de sélection et à ses divers développements. Nous avons d'abord étudiée sa forme élémentaire (la sélection simple), puis sa forme générale (la sélection avec critères). Nous avons ensuite découvert la notion de jointure, qui s'introduit naturellement lorsque la sélection porte sur plusieurs tables, et de là nous sommes passés à la correspondance et à la non-correspondance. Nous avons enfin perfectionné la sélection grâce à la notion de regroupement, ce qui nous a permis d'effectuer des synthèse, et de manipuler les doublons. Bref, comme nous pouvons le constater, la sélection est la reine des requêtes !          
Cependant, la 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.
Comme pour les autres chapitres de ce tutoriel (ou tutorial, ou cours en ligne), nous utilisons le SGBD Access comme support pratique.
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.          
U V T
 5              
 6    
 7    
   
U V W
 1              
 2    
 3    
 4    
   
U V W
 1              
 2    
 3    
 4    
 5  
 6  
 7  
Table n°2 Table n°1 Résultat
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.
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. 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...". 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). 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.
Requête ajout simple
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").
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 (chapitre 22). 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.          
Nom Prénom
Durand Oscar
Lechant Anne
   
Nom Prénom Date
Machin Pierre 12/6/1983
Truc Nathalie 26/11/1985
Chose André 5/2/1980
   
Nom Prénom Date
Machin Pierre 12/6/1983
Truc Nathalie 26/11/1985
Chose André 5/2/1980
Durand Oscar
Lechant Anne
Table n° 2
(avant & après)
Table n° 1 (avant) Table n° 1 (après ajout
de la table n° 2)
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 :
Requête ajout avec sélection de champs
car le 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.
Nom Prénom Date
Machin Pierre 12/6/1983
Truc Nathalie 26/11/1985
Chose André 5/2/1980
    
Nom Prénom
Durand Oscar
Lechant Anne
   
Nom Prénom
Machin Pierre
Truc Nathalie
Chose André
Durand Oscar
Lechant Anne
Table n° 1
(avant & après)
Table n° 2
(avant)
Table n° 2 (après
ajout table n° 1)
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) :
Requête ajout avec critères
Et voici le résultat :
Nom Prénom
Durand Oscar
Lechant Anne
Machin Pierre
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 (cet objet est étudié dans les chapitres 26 et suivant), 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.
Nom Prénom Date
Machin Pierre 12/6/1983
Truc Nathalie 26/11/1985
Chose André 5/2/1980
   
Nom Prénom Date
 
   
Nom Prénom Date
Machin Pierre dimanche 12 juin 1983
Truc Nathalie mardi 26 novembre 19685
Chose André mardi 5 février 1980
Table n° 1
(avant & après)
Table n° 3
(copie du modèle)
Table n° 3 (après ajout
de la table n° 1)
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 (chapitre 18). 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. Nous trions la table de départ (Table1) par ordre de CA décroissant. Nous l'ajoutons à une table vide contenant les mêmes champs, plus un champ de type NuméroAuto (Table2). Puis, à l'aide d'une requête de sélection simple, nous trions la table Table2 par ordre alphabétique du premier champ. 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).
Produit CA
prod01 12 345,00 €
prod02 67 890,00 €
prod03 527,12 €
prod04 92 187,55 €
   
Produit CA
prod04 92 187,55 €
prod02 67 890,00 €
prod01 12 345,00 €
prod03 527,12 €
   
Produit CA Classement
(NuméroAuto)
Table1 Table1 (triée) Table2 (avant ajout)
Produit CA Classement
prod04 92 187,55 € 1
prod02 67 890,00 € 2
prod01 12 345,00 € 3
prod03 527,12 € 4
   
Produit CA Classement
prod01 12 345,00 € 3
prod02 67 890,00 € 2
prod03 527,12 € 4
prod04 92 187,55 € 1
Table2 (après ajout de Table1) Table2 (triée)
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 (voir le chapitre 18).
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. Lecas 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.          
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.
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 :
Message de requête ajout
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 de regroupement (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.          
U V W
 a           1 
 b    3
 a  2
 b    1
 c    1
 b    2
  
U W1 W2 W3
 a                    
 b      
 c  
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.
Société Prod Rang
Machin prod1 1
Machin prod4 2
Machin prod12 4
Truc prod2 1
Truc prod5 3
Machin prod21 3
Truc prod6 2
Chose prod2 2
Chose prod30 1
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 :
            nous indiquons d'abord sur quelle table nous voulons opérer. Dans le cas présent, il s'agit de la table "Table1" ;
  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 ;
  nous choisissons le champ "Rang" comme "en-tête de colonne". Cela signifie que le SGBD va créer les colonnes "1", "2", etc. ;
  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 ;
  nous cliquons sur le bouton "Terminer" et nous basculons en mode feuille de données pour examiner le résultat (figure ci-dessous).
Société 1 2 3 4
Chose prod30 prod2
Machin prod1 prod4 prod21 prod12
Truc prod2 prod6 prod5
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.          
Chapitre précédent Plan du tutoriel Liste des tutoriels Chapitre suivant
Accueil | Technique | Liens | Actualités | Formation | Emploi | Forums | Base
Copyright © CERIG/EFPG 1996-2003
Réalisation et mise en page : J.C. Sohm