Requêtes analyse croisée

Une requête analyse croisée est l'équivalent du tableau croisé dynamique dans Excel. Elle permet d'obtenir des statistiques par ligne et par colonnes. La seule restriction par rapport à Excel est que dans Access, on ne peut avoir qu'un champ de regroupement par colonne

Une requête analyse croisée peut se fonder sur plusieurs tables

Elle se compose de 3 éléments minimum (3 champs minimum donc également) :

Dans notre exemple :

Un champ Sexe, Opération Regroupement, en entête de ligne (mais on peut avoir autant d'en-têtes de lignes qu'on veut)
Un champ Genre, Opération regroupement, comme en-tête de colonne (un seul en-tête de colonne autorisé)
Un champ ResteStock, opération SUM, COUNT, AVG, etc. (mais pas regroupement) qui va montrer les statistiques désirées (il est donc souvent mieux d'0avoir affaire à un champ de type numérique (comme ici ResteStock) afin de pouvoir utilier une autre fonction que COUNT)

Dans l'exemple simple suivant, nous avons la table suivante :

IDJouet	Libelle	Sexe	AgeMin	Genre	AvecPile	ResteStock	PU
1	Ours en peluche	Fille	66	Poupées et peluches	Non	9	CHF 16.50
2	Train électrique	Garçon	8	Divers	Oui	2	CHF 189.00
3	Boomerang	Garçon	6	Dînette	Non	20	CHF 12.90
4	assiette	Fille	2	Dînette	Non	80	CHF 5.00
5	MarioLand	Garçon	8	Jeux Vidéo	Non	4	CHF 89.90
6	Monopoly	Mixte	12	Jeux de société	Non	6	CHF 99.00
7	7 familles	Mixte	5	Jeux de société	Non	8	CHF 6.75
8	Goldorak	Garçon	6	Poupées et peluches	Oui	10	CHF 29.00
9	Barbie à la mer	Fille	4	Poupées et peluches	Non	2	CHF 21.00
10	voiture électrique	Garçon	6	Divers	Oui	1	CHF 229.00
11	Casino Deluxe	Mixte	18	Jeux Vidéo	Non	0	CHF 109.90
12	Casimir	Mixte	5	Poupées et peluches	Non	2	CHF 19.90
13	Playmobil	Mixte	3	Divers	Non	13	CHF 9.90
14	Voiture télécommandée	Garçon	10	Divers	Oui	4	CHF 65.00
15	ballon	Mixte	2	Divers	Non	25	CHF 8.60

A partir de là, nous aimerions obtenir la statistique suivante : Combien d'articles nous reste-t-il pour chaque catégorie de jouet, et pour chaque sexe :

Pour ce faire, nous avons besoin de la requête style Analyse Croisée suivante :

Qui est représenté par le code SQL Suivant :

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock
SELECT T_Jouet.Sexe
FROM T_Jouet
GROUP BY T_Jouet.Sexe
PIVOT T_Jouet.Genre;

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock	Cette instruction est exclusive à la requête analyse croisée. Le titre de ce champ SommeDeResteStock n'apparaît pas dans le résultat (seulement les données). On peut donc utiliser les fonctions de regroupement habituelles : SUM, COUNT, MAX, MIN, AVG, Etc.
SELECT T_Jouet.Sexe	Ce titre apparaître ligne par ligne, autant de fois qu'il existe de sexe. Dans notre exemple, nous aurions peut-être dû mettre le Genre en ligne, pour éviter d'avoit un tableau plus large que haut
FROM T_Jouet	Tables de base servant à l'élaboration de la requête. On peut bien entendu avoir plusieurs tables (dans le cas ou par exemple, on désirerait avoir un regroupement des achats des clients par genre d'article, ce qui demanderait une table T_Client, T_Vente, T_VenteDetail et T_Article)
GROUP BY T_Jouet.Sexe	Cette ligne permet de créer aurant de colonnes que nécessaires (une colonne par Sexe différent (ici, 3 colonnes : Garçon, Fille, Mixte))
PIVOT T_Jouet.Genre;	Ce champ doit être unique (pas question d'avoir PIVOT Genre, AvecPile). C'est la détermination du nombre de colonnes et le titrage de chacune d'entre elle avec un genre différent

Il est possible d'affiner le tableau en demandant plusieurs champs comme en-tête de ligne (mais pas comme en tête de colonne représentées pas PIVOT). Par exemple, si on voulait presque la même statistique, on aurait ceci :

Dans cet exemple, nous avons 2 en-têtes de ligne : Genre et AvecPile (dans l'exemple précédent, Genre était une en-tête de colonne). Nous obtenons donc les articles restants pour chaque genre, et, AvecPile oui ou non. On constate donc que tout n'est pas représenté, simplement parce qu'il existe certains genres d'articles qui n'existent qu'avec ou sans piles (Le genre Dînette n'existe que sans pile, tandis que le genre Poupées et peluches comprend cerzains articles avec et d'autres sans piles)

TRANSFORM Sum(T_Jouet.ResteStock) AS SommeDeResteStock
SELECT T_Jouet.Genre, T_Jouet.PileNecessaire
FROM T_Jouet
GROUP BY Genre, AvecPile
PIVOT T_Jouet.Sexe;

Le GROUP BY contient donc autant d'enregistrement que souhaité

Problème des noms de colonnes dynamiques

Comme on peut le constater, les noms de colonnes sont variables : Non seulement leur nom, mais aussi leur nombre (on peut imaginer un jour changer les sexes, et tout a coup avoir comme sexes : Petits garçons, petites filles, mixte, adultes).

Cela ne pose a priori pas de problème dans la requête.

Cela ne poserait pas de problème non plus dans un simple formulaire.

Mais dans le cas ou cette requête serait la source d’un sous-formulaire, le problème des champs dynamiques se pose.

Admettons le cas (peu probable, mais il faut bien donner un exemple), que nous concevions un formulaire principal, avec pour seul champ " PileIncluse ", qui serait le champ père dans le formulaire principal et le sous formulaire.

Lors du lancement du formulaire principal, Access va nous renvoyer un message d’erreur qui nous demande de fixer les en-têtes de colonnes dans la requête source (ce qui est compréhensible, puisque comme on a vu, les sexes peuvent changer)

Solution

Aller dans la requête, demander les propriétés de la requête, et dans la propriété En-têtes des colonnes, écrire : "Garçon";"Fille";"Mixte"

Ce qui force les titres des colonnes à devenir statiques