Discussion :

[Faro]

Bonjour,

Je bloque sur un problème et je me permet de vous demander ici conseil.

J'ai une requête d'analyse croisée généré par code VBA comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
    Dim Sql As String                             ' declare la variable Sql
 
    Sql = "TRANSFORM Sum([998_REQCOMP].LIG_Val) AS SommeDeLIG_Val"
    Sql = Sql & " SELECT [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib, Sum([998_REQCOMP].LIG_Val) AS [Total de LIG_Val]"
    Sql = Sql & " FROM 998_REQCOMP"
    Sql = Sql & " GROUP BY [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib"
    Sql = Sql & " PIVOT Format([LIG_Date], 'mm/yyyy') In ('01/2007','02/2007','03/2007','04/2007','05/2007','06/2007','07/2007','08/2007','09/2007','10/2007','11/2007','12/2007');"       ' compose la chaîne SQL
 
    DoCmd.DeleteObject acQuery, "998_Anacroi"    ' supprime la requête AnalyseAnnée
 
    CurrentDb.CreateQueryDef "998_Anacroi", Sql  ' crée la nouvelle requete
    DoCmd.OpenQuery "998_Anacroi"                ' ouvre la requete

Cette requête fonctionne bien, et me sort les bonnes informations. Je la génère en VBA avec l'objectif sournois () de changer de manière dynamique l'année du Pivot (et donc les entêtes).

Tout cela fonctionne donc très bien ... MAIS ... si je passe les Pivot de 2007 à 2008 ... l'état ne fonctionne plus !

Je ne sais vraiment pas comment attaquer ce problème ... ma démarche est mauvaise au niveau de la génération de la requête, ou il existe des possibilités de faire des états dynamique ?

Merci beaucoup,

Faro

[ilank]

Bonjour,
le code SQL ne semble pas poser de problème. Le problème se pose au niveau de l'état qui a besoin de référencer les sources des contrôles aux colonnes de la requête, or en changeant d'année dans le pivot tu modifies le nom des colonnes; l'état n'est plus correctement référencé.
En clair, là où un contrôle de l'état affiche la colonne '01/2007' de la requête il doit maintenant afficher la colonne '01/2008'. Donc, lors de la création de la requête, ouvre en mode design l'état et modifie la propriété controlsource des contrôles avec le bon nom de colonne.

[Faro]

Et on peut "automatiser cela" (a savoir quand on change la date, que la vue soit automatiquement changé ?) ? une attaque en VBA je pense ?

Faro

[User]

Bonsoir,

Un problème que l'on a déjà rencontré sur le forum:

Si tu veux faire cela dynamiquement, tu peux mettre à jour la source de tes contrôles à l'ouverture de ton état:

Un exemple:

Tu donnes les noms "Entete1","Entete2"...,"Entete12" à tes entêtes de colonnes.

et tu donnes les noms "Mois1","Mois2"...,"Mois12" à tes textbox de tes colonnes.

Code :

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
Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
Dim An As Integer ' Année qui peut-être un paramètre
Dim list As String ' liste des mois a mettre dans le sql
Dim i As Integer
 
An = 2008
list = vbNullString
 
For i = 1 To 12 'parcourt les 12 mois
Me("Entete" & i).Caption = Format(i, "00") & "/" & An ' affecte les mois aux entêtes de colonnes (ex: '01/2008')
Me("Mois" & i).ControlSource = Format(i, "00") & "/" & An ' affecte les noms du champs, les noms des colonnes de la RAC (ex: '01/2008') à la source des textbox
list = list & "'" & Format(i, "00") & "/" & An & "'" & "," ' ajoute les mois à la liste 
Next i
 
list = Left(list, Len(list) - 1) ' enlève le dernier caractère ,
 
SQL = "TRANSFORM Sum([998_REQCOMP].LIG_Val) AS SommeDeLIG_Val"
    SQL = SQL & " SELECT [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib, Sum([998_REQCOMP].LIG_Val) AS [Total de LIG_Val]"
    SQL = SQL & " FROM 998_REQCOMP"
    SQL = SQL & " GROUP BY [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib"
    SQL = SQL & " PIVOT Format([LIG_Date], 'mm/yyyy') In (" & list & ");"       ' compose la chaîne SQL
 
Me.RecordSource = SQL 'affecte le sql à la source de l'état
 
End Sub

A+

[User]

Une autre possibilité mais qui ne te donnera pas toutes lignes dans ton état:
Tu fais un critère sur l'année ce qui va te limiter ton nombre de lignes,
et après dans ton sql tu utilise la liste des mois (1,2,3,4,5,6,7,8,9,10,11)

donc la source de tes textbox (les colonnes de ta RAC) sera (1,2,3,4,5,6,7,8,9,10,11)

et le code sur ouverture de ton état:

Code :

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
Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
Dim An As Integer ' Année qui peut-être un paramètre
Dim i As Integer
 
An = 2008
list = vbNullString
 
For i = 1 To 12 'parcourt les 12 mois
Me("Entete" & i).Caption = Format(i, "00") & "/" & An ' affecte les mois aux entêtes de colonnes (ex: '01/2008')
Next i
 
SQL = "TRANSFORM Sum([998_REQCOMP].LIG_Val) AS SommeDeLIG_Val"
    SQL = SQL & " SELECT [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib, Sum([998_REQCOMP].LIG_Val) AS [Total de LIG_Val]"
    SQL = SQL & " FROM 998_REQCOMP"
    SQL = SQL & " WHERE Year([LIG_Date])=" & An
    SQL = SQL & " GROUP BY [998_REQCOMP].SCI_Nom, [998_REQCOMP].Nom_Lib"
    SQL = SQL & " PIVOT Month([LIG_Date]) In (1,2,3,4,5,6,7,8,9,10,11,12);"       
 
Me.RecordSource = SQL 'affecte le sql à la source de l'état
 
End Sub

Il y a d'autre possibilités mais il faudrait en connaitre plus sur tes requêtes et de tes tables..

A+

[Faro]

Oula, un grand merci pour ses deux exemples !!!

JE vais regarder cela rapidement, je vous tiens au courant des résultats

Faro