Discussion :

[babymeosh]

Bonjour,

En premier lieu, je dois dire que je ne suis pas vraiment bon à ACCESS ... Je l'utilise au travail pour automatiser certains rapports/ bulletins que je produis et ça m'est vraiment utile ... Jusqu'à maintenant !! Je suis bloqué sur un travail que j'essaie de faire depuis de nombreux mois, je n'arrive pas à faire fonctionner le tout.

Je plante le décor.
Je produis tous les trimestres un bulletin sur les échanges extérieurs de marchandises. Pour ce faire J'ai une base de données ACCESS (accdb, Access 2013) contenant des tables de données et des tables-dictionnaires et je dois en extraire des tableaux sous un format précis.
Pour simplifier les choses, j'ai créé une maquette Excel qui contient du code VBA qui exécute pour le trimestre choisi, de la base ACCESS des requêtes stockées dans des fichiers textes (Parce que réécrire toutes les requêtes, environ cinquante, dynamiques en VBA m’ennuyait prodigieusement :p ). Ensuite le programme transfère les recordsets sur des feuilles Excel, fais un peu de mise en forme et enregistre le tout dans un nouveau classeur nommé de la date et de l’heure d’extraction. J'ai déjà fait la plupart des tableaux i.e. des requêtes SQL correspondantes, qui fonctionnent bien, mon VBA également mais il reste encore certains tableaux (leurs requêtes SQL) qui me sont difficiles à mettre en œuvre...

L'OS en question
Je dois faire un tableau qui présente en colonnes les variations trimestrielles des cinq derniers trimestres et variation annuelle du trimestre en cours, pour les biens exportés / importés, classés par produits.

Donc, au début, j’ai essayé de faire une requête qui donne pour chaque année et trimestre, les variations trimestrielles de par code produit. Le code SQL ressemble à ceci:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT Courant.anm, 
       Courant.ctci4, 
       Courant.maxa, 
       Sum(Iif(( ( Courant.valeur - Ancien.valeura ) / Ancien.valeura ) * 100 IS 
               NULL, 
           0, ( 
               ( Courant.valeur - Ancien.valeura ) / Ancien.valeura ) * 100)) AS 
       VTR 
FROM   (SELECT B.annee, 
               B.mois, 
               B.annee & B.mois                                     AS ANM, 
               (SELECT Max(base1.annee & base1.mois) 
                FROM   base1 
                WHERE  base1.annee & base1.mois < B.annee & B.mois) AS MaxA, 
               B.ctci4, 
               Sum(B.valstat)                                       AS Valeur 
        FROM   base1 B 
        WHERE  ( Len(B.ctci4) ) = 1 
               AND ( B.flux = "e" 
                      OR B.flux = "r" ) 
        GROUP  BY B.annee, 
                  B.mois, 
                  B.ctci4) AS Courant 
       INNER JOIN (SELECT base1.annee & base1.mois AS ANM, 
                          base1.ctci4, 
                          Sum(base1.valstat)       AS ValeurA 
                   FROM   base1 
                   WHERE  ( Len(base1.ctci4) ) = 1 
                          AND ( base1.flux = "e" 
                                 OR base1.flux = "r" ) 
                   GROUP  BY base1.annee & base1.mois, 
                             base1.ctci4) AS Ancien 
               ON ( Courant.ctci4 = Ancien.ctci4 ) 
                  AND ( Courant.maxa = Ancien.anm ) 
GROUP  BY Courant.anm, 
          Courant.ctci4, 
          Courant.maxa

Cette partie de la requête fonctionne bien. En fait, j’ai construit un ID en concaténant l’année et le trimestre(trimestre allant de 21 à 24, Courant.ANM), pour l’utiliser comme jointure interne une avec une autre copie de la même table pour être en mesure de présenter sur le même enregistrement la valeur actuelle, la valeur du trimestre précédent, le code produit (à un chiffre), l'ID de la période précédente (année et trimestre concaténé) et le champ VTR qui calcule la variation à la période précédente en pourcentage.

Pour finir, je dois faire un tableau croisé dynamique en utilisant un TRANSFORM. Je dois mettre en entête de colonne, pour le moment l'année et le trimestre (ANM), le code du produit (CTCI4) en entête ligne et en valeur le champ VTR i.e. les variations trimestrielles. Mais quand j’essaie, MS ACCESS dit que B.annee n’est pas reconnu comme un nom de champ valide. Même si je le nomme différemment ce champ, ça ne fonctionne pas !! S'il vous plaît quelqu'un peut-il dire comment le faire?

[Didier L]

Bonsoir,

Est-il possible de poster la clause SQl avec le "TRANSFORM" ?

Merci

[babymeosh]

Enfin quelqu'un !!! Merci merci triple merci !!! En fait la portion que je poste n'est qu'une partie de mon code entier mais en debuggant mon code je l'ai réduit à cette portion qui pose problème. Le premier code posté marche, mais en passant au pivot, il ne marche plus du tout ...

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
TRANSFORM Sum(IIf(((Courant.valeur-Ancien.valeura)/Ancien.valeura)*100 Is Null,0,((Courant.valeur-Ancien.valeura)/Ancien.valeura)*100)) AS VTR
SELECT Courant.ctci4
FROM (SELECT B.annee, 
               B.mois, 
               B.annee & B.mois                                     AS ANM, 
               (SELECT Max(base1.annee & base1.mois) 
                FROM   base1 
                WHERE  base1.annee & base1.mois < B.annee & B.mois) AS MaxA, 
               B.ctci4, 
               Sum(B.valstat)                                       AS Valeur 
        FROM   base1 B 
        WHERE  ( Len(B.ctci4) ) = 1 
               AND ( B.flux = "e" 
                      OR B.flux = "r" ) 
        GROUP  BY B.annee, 
                  B.mois, 
                  B.ctci4)  AS Courant, (SELECT base1.annee & base1.mois AS ANM, 
                          base1.ctci4, 
                          Sum(base1.valstat)       AS ValeurA 
                   FROM   base1 
                   WHERE  ( Len(base1.ctci4) ) = 1 
                          AND ( base1.flux = "e" 
                                 OR base1.flux = "r" ) 
                   GROUP  BY base1.annee & base1.mois, 
                             base1.ctci4)  AS Ancien
GROUP BY Courant.ctci4, Courant.maxa
PIVOT Courant.anm;

[Didier L]

Bonjour,

La clause SQl est bien complexe ;-/
Dans Access, afin de simplifié, au lieu de faire des requêtes imbriquées, tu peux aussi enregistrer une sous requête (et ainsi la tester) puis la réutiliser dans une seconde ainsi par exemple tu peux enregistrer tes deux clauses pour Courant et Ancien et les tester indépendamment.
On obtiendrait la chose suivante :

Courant :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT B.annee, B.mois, B.annee & B.mois AS ANM, 
         	(SELECT Max(base1.annee & base1.mois) FROM   base1 
                WHERE  base1.annee & base1.mois < B.annee & B.mois) AS MaxA, 
               B.ctci4, 
               Sum(B.valstat) AS Valeur 
        FROM   base1 B 
        WHERE  ( Len(B.ctci4) ) = 1 AND ( B.flux = "e" OR B.flux = "r" ) 
        GROUP  BY B.annee, B.mois, B.ctci4

Et la je me pose la première question : comment cela peut-il fonctionné avec une clause Group By sur 3 champs B.annee, B.mois, B.ctci4 alors que nous n'avons pas d'opération de regroupement sur le champ composé ANM ni sur MaxA ???
Cette requête fonctionne t'elle toute seule ?

Ancien :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT base1.annee & base1.mois AS ANM, base1.ctci4, Sum(base1.valstat) AS ValeurA 
                   FROM   base1 
                   WHERE  ( Len(base1.ctci4) ) = 1 AND ( base1.flux = "e" OR base1.flux = "r" ) 
                   GROUP  BY base1.annee & base1.mois, base1.ctci4

Semble plus juste ;-)

Et on aurait alors :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sum(IIf(((Courant.valeur-Ancien.valeura)/Ancien.valeura)*100 Is Null,0,((Courant.valeur-Ancien.valeura)/Ancien.valeura)*100)) AS VTR
SELECT Courant.ctci4
From Courant, Ancien
GROUP BY Courant.ctci4, Courant.maxa
PIVOT Courant.anm

Courant et ancien étant les nom que tu aurais donné à tes requêtes sources dans Acces.
Et la il me semble qu'il doit manquer une jointure ou une clause Where ?
Comment faire le lien entre les deux tables issues de tes sous requêtes ?

Je n'ai pas la solution car je n'ai pas tes données sous les yeux et n'ai pas vraiment compris ce que tu cherchais a faire, mais un petit conseil, avant de faire une requête imbriqué, test toutes tes sous requêtes individuellement ;-)

Au plaisir de lire la suite ...

[babymeosh]

Une fois encore merci pour le temps pris pour lire mon code, indigeste je sais .
Je vais revenir sur le premier code que j'ai posté car c'est de ce code que j'aimerais faire un pivot. En fait il marche. Il tourne assez lentement, mais à la fin, je me retrouve avec un résultat de ce genre:
Courant. ANM, Courant.CTCI4, Courant.MaxA et VTR.

Le champ ANM Contient L'année et le trimestre concaténés, le trimestre étant codé de '21' à '24'. CTCI4 contient un code produit (d'un chiffre, d'où le "len" que j'ai utilisé à un certain niveau du code). Courant.MaxA me permet d'avoir cette fois-ci les champs concaténés année et trimestre précédant le trimestre affiché dans Courant.ANM. VTR contient la variation en pourcentage par rapport au trimestre précédent (Mon tableau principal, Base1 contient un champ valeur qui est la source du calcul). Je sais c'est difficile a expliquer mais je m'attèlerai a être le plus explicite possible. En exemple je poste cette image.



En fait J'utilise les champs ANM et MaxA que j'ai crées pour pouvoir par un INNER JOIN sur ces deux champs avoir, par exemple sur le produit '0' la valeur exporté au 2e trimestre 2014 (soit Courant.ANM='201422') et celle du premier trimestre (Courant.MaxA='201421') le tout dans un même tableau. Comme ca c'est plus facile de calculer la variation VTR.

Cette requête marche bien, la preuve en images plus haut. Mais le souci se pose quand je passe à son pivot, le système ne reconnaît plus un de mes champs.
J'ai déjà essayé d'enregistrer le résultat de la première requête que j'ai posté sous une table et de passer à son pivot, ce qui a très bien marché. Mais directement ca bugge.

En fait je dois lancer ces requêtes tous les trimestres et j'ai déjà préparé un complément de code sous VBA pour mettre à jour les champs concernant l'année et le trimestre sur instruction de l'utilisateur, c'est ce pourquoi j'ai du mal a enregistrer cette requête d'abord dans la base.

S'il est possible de la réecrire en utilisant des tables tampons qui seront détruits à la fin de la requête, ca me serait aussi parfait. Là, je sauvegarde d'une part le tableau à passer en transform, j'exécute mon transform que j'exporte et ensuite je supprime la table tampon. Si tu en as besoin je peux uploader une copie light de ma base!!

[Didier L]

Bonsoir,

Je pense effectivement que la requête est complexe pour passer en pivot.
Si tu as en plus du code VBA qui gère tout cela ne te prends pas la tête.

Tu peux enregistrer tes requêtes de base, jusqu'à ta requête finale et cette dernière créera ta table temporaire grâce au type de requête "création de table". Chaque fois que cette requête s'exécutera elle remplacera la table précédemment créé (elle écrase).
Pour lancer cette requête finale création de table utilise en VBA la syntaxe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Currentdb.execute "LeNomdetaRequete"

Si ta requête création de table se base sur des requêtes qui doivent récupérer des valeurs saisies par l'utilisateur, pour ma part je passe par des variables globales et des fonctions.
Exemple :
Dans un module de déclaration

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Global Dim vsMonAnnee as string
Dim vsMonTrimestre as string

Après la saisie de l'opérateur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
vsMonAnnee= [Champ année choisie]
vsMonTrimestre=[Champ Trimestre choisi]

Dans un module d'utilitaire (ou le même module) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Function RecupAnnee() as string
RecupAnnee=vsMonAnnee
End function
 
Function RecupTrimestre() as string
RecupTrimestre=vsMonTrimestre
End function

Et enfin dans tes requêtes de base la ou tu a besoin de faire référence à ton année et ton trimestre, tu peux directement utilisé tes fonction, soit dans les critères ou dans les champs avec l'appel classique :
exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RecupAnnee() & RecupTrimestre()

Ensuite il ne te restera plus qu'a faire ta requête croisée sur ta table temporaire créée

En esperant t'avoir offert une solution simple

[babymeosh]

On est sur la même longueur d'onde, ou presque!

EN fait j'ai déjà implémenté tout le reste des astuces que tu as évoqué, c'était juste comment faire tourner cette requête qui me cassait la tête. Je vais encore être beaucoup plus précis, et décrire un peu.

Jai un classeur Excel qui prends en charge des macros. C'est dan ce classeur que j'ai développé mon interface utilisateur. L'utilisateur entre l'année et le trimestre pour lequel il voudrait que mes 50 tableaux soient produits. Le code VBA derrière se connecte à mon fichier Access via du ADO, exécute du code SQL en actualisant les clauses "WHERE" selon les paramètres entrés. J'ai préféré rester en Excel parce que après mon "CopyFromRecordset" du recordset, je fais un peu de mise en forme de chaque tableau et enfin j'exporte le tout vers un nouveau fichier Excel que je nomme avec l'année, le trimestre et l'heure à laquelle elle est crée.

Mais ton idée parait super simple!! Je vais encore creuser la partie création de table !! Je te reviens dans un moment !!!

[Didier L]

Bonjour,
Si je comprend bien, ton problème majeur sera de transmettre des informations d'Excel vers Access (Critère de sélection, etc ...)
Quelle syntaxe utilise tu pour piloter Access depuis Excel ?
As tu essayé depuis Excel de manipuler la collection QueryDefs d'access ?
Cela te permet de modifier la clause SQL d'une requête et donc de pouvoir manipuler tes clause Where (de les réécrire à la volée)
Est-il possible d'exécuter une requête "Action" comme avec la syntaxe ".execute" d'Acess ?
Un autre solution, serait aussi d'avoir une table de paramètre dans access.
Dans cette table tu écris sur une seule ligne juste , les différent critère qui provienne d'Excel et tu t'en sert dans tes requête pour tes critère.
Soit dans la requête elle même en faisant référence à la table sans aucune jointure, soit grâce au fonction VB qui récupère les valeur de la table au lieu des variables globale.
Ainsi, depuis Excel, tu écris dans ta table, avec des requêtes SQL, Delete, pour la vider, et Insert pour écrire ta ligne ou une clause Update pour mettre a jour un pou plusieurs champ

Bref, les solution sont multiples et tu en trouvera bien une

[babymeosh]

Merci merci!!! J'ai réussi! Finalement. Je crée une table avec un "SELECT * INTO " et après, toujours via ma connexion ado je la supprime avec un "DROP TABLE". Ca me permet d'exécuter ma requête pivot, de la passer dans un recordset et de la supprimer. Lent, mais ca marche!! C'est tout ce qu'il me fallait !!! Merci pour ton inestimable aide !! Je mets en résolu !! Wiiii !!!!!