|
Chapitre 15 :
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. |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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. |
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 (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. |
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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). |
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
||||||
|
|
|||||||||
|
|
|||||||||
|
|
|||||||||
|
|
|
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 : |
|
||||||
|
|
|
|
|
||||||
|
|
|
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. |
|
||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 |
|
||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||
|
|
|
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. |
|
|
|
|||