Comment créer des requêtes d'analyses croisées conditionnelles

Pour commencer nous allons définir les objets nécessaires pour la réalisation de l'exercice.
Ouvrez un fichier de base de données Microsoft ACCESS vierge. Commencez par créer un formulaire que vous nommerez Analyse Croisée.

Dans ce formulaire, ajoutez une zone de liste déroulante nommée Zl_critere. Celle-ci contiendra le critère pour la requête d'analyse croisée.
Les propriétés de Zl_critere sont les suivantes :

Sauvegardez le formulaire, passez-le en mode utilisation et sélectionnez la valeur 2001 dans la zone liste. La valeur de cette zone liste constituera notre critère.

Créez une table nommée Vente avec les champs suivants :

Et comme une table ACCESS n'est jamais vide, saisissez les enregistrements ci-dessous.

Pour la requête d'analyse croisée, nous allons faire très simple. Ce n'est qu'un exercice.

Ouvrez une nouvelle requête et insérez la table Vente.
Fermez le panneau d'insertion de table.
Sélectionnez dans le menu Requête l'item Analyse Croisée.
Puis faites glisser les champs de la table un à un comme l'exemple présenté ci-dessous :

Testez la requête et sauvegardez-la sans la fermer.

Nous allons tenter à présent de lier la zone liste contenant le critère à notre belle requête. Pour cela nous allons devoir rajouter du code dans la cellule Critère de la dernière colonne à la place du 2001.

                [formulaires]![Analyse Croisée].[Zl_Critere]

Exécutez la requête… Une erreur se produit dont le message est plus qu'explicite.

Nous venons de démontrer que la requête d'analyse croisée dans Access ne supporte pas les critères liés. Si vous remplacez le critère "[forms]![Analyse Croisée].[zl_critere]" par "2001" vous verrez que cela fonctionne. Vous pouvez essayer avec une requête Select intermédiaire pour le critère, le résultat est malheureusement identique, à cause de la référence faite à un contrôle de formulaire.

Ce problème peut être contourné de trois manières.
La première consiste à utiliser le tableau des paramètres clic droit/Paramètres de la requête et d'y inscrire les liens vers les contrôles. [forms]![Analyse Croisée].[zl_critere] n'oubliez pas de mettre le type de données dans la 2^e colonne !
Il arrive que sur de grosses requêtes trop complexes, Access éprouve des problèmes d'exécution. Dans ce cas il faut utiliser d'autres méthodes.
La deuxième est à base de requêtes. En pleine contradiction avec notre démonstration ?!. Pas tout à fait puisqu'elle consiste à créer une table temporaire ne contenant que les enregistrements concernés pour l'analyse. Facile à mettre en œuvre, car sans utilisation du VBA, elle peut être automatisée dans une macro.
L'autre fait appel au VBA et à l'avantage d'être plus rapide à exécuter sur de gros volumes de données, car ne mettant en œuvre qu'une seule requête.

Pour créer la première requête, procédez de la même manière que pour la création d'une requête de type Select, suivant le modèle présenté ci-dessous.

Cette requête permet de sélectionner les enregistrements à traiter dans la requête d'analyse croisée. À l'aide du menu Requête transformez-la en Requête Création de table…

Sauvegardez la requête sous le nom suivant : Gen_Temp_AC.

Sauvegardez celle-ci sous Analyse Résultat par An
Nos objets sont prêts :

Bien que cette procédure puisse être réalisée dans une macro, nous allons plutôt la réaliser en VBA.
Passez le formulaire Analyse Croisée en mode création. Créer un bouton de commande que vous nommez Btn_FullQueries et qui porte Analyse Croisée Full Queries comme légende.
Sur l'évènement Sur clic sélectionnez [Procédure événementielle] puis cliquez sur le bouton pour créer une procédure VBA.

                        DoCmd.SetWarnings False                          ' message off
                        DoCmd.DeleteObject acTable, "Temp_AC"            ' supprime la table temporaire
                        DoCmd.OpenQuery "Gen_Temp_AC", acViewNormal      ' création de la table temporaire
                        DoCmd.OpenQuery "Analyse Croisée", acViewNormal  ' affiche l'analyse croisée
                        docmd.setWarnings True                           ' message on

La méthode à base de requête est maintenant terminée. En sélectionnant le critère dans la zone liste et en cliquant sur le bouton vous aurez une belle requête d'analyse croisée basée sur le critère de la liste.

Créez la requête d'analyse croisée identique à la première réalisée.

Passez en mode SQL à l'aide du bouton liste de la barre d'outils requête.

Le code SQL apparaît faites-en une copie à l'aide de la combinaison de touches Ctrl+C.

Enregistrez la requête sous le nom AnalyseAnnée.

Dans le formulaire, créez un deuxième bouton que vous nommez Btn_FullVBA avec la légende Analyse Croisée Full VBA. Créez de la même manière que pour le premier bouton une procédure VBA sur l'évènement Sur clic, insérez la chaîne SQL précédemment copiée.

                TRANSFORM Sum(Vente.Montant) AS SommeDeMontant
                SELECT Vente.Vendeur
                FROM Vente
                WHERE (((Vente.Année)=2001))
                GROUP BY Vente.Vendeur
                PIVOT Vente.Année;

Modifiez le code comme suit en respectant bien les espaces après les doubles-quotes.

                Dim Sql As String                             ' déclare la variable Sql
                
                Sql = "TRANSFORM Sum(Vente.Montant) AS SommeDeMontant"
                Sql = Sql & " SELECT Vente.Vendeur"
                Sql = Sql & " FROM Vente"
                Sql = Sql & " WHERE (((Vente.Année) = 2001))"
                Sql = Sql & " GROUP BY Vente.Vendeur"
                Sql = Sql & " PIVOT Vente.Année;"         ' compose la chaîne SQL
                
                DoCmd.DeleteObject acQuery, "AnalyseAnnée"    ' supprime la requête AnalyseAnnée

                CurrentDb.CreateQueryDef "AnalyseAnnée", Sql  ' crée la nouvelle requête
                DoCmd.OpenQuery "AnalyseAnnée"                ' ouvre la requête

Dernière modification du code et la plus importante, elle concerne le remplacement du critère statique (2001) par le critère dynamique Me.Zl_critere.
Cette instruction fait référence à la valeur de la zone liste Zl_critere du formulaire courant (me).
Elle peut également être écrite Me.Zl_critere.value ou bien Zl_critere.value.

                'Remplacez cette ligne
                Sql = Sql & " WHERE (((Vente.Année) = 2001))"
                
                'Par celle-ci    
                Sql = Sql & " WHERE (((Vente.Année) = " & Me.Zl_critere & "))"

Notez que l'on peut également modifier le code SQL d'une requête en faisant référence à la propriété SQL de l'objet DAO.QueryDef dont un excellent exemple est présent dans la FAQ.