Discussion :

[gegematic]

Bonjour,
J'ai une table avec (entre autre) 2 colonnes, defaut_1 et defaut_2, qui peuvent contenir des valeurs venant d'un autre table, [Val-defaut]
Je fait deux requête croisée dynamique (Rq1 et Rq2) avec comme pivot,
successivement defaut_1, puis defaut_2

Code :

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

1
2
3
4
5
TRANSFORM (Count([Annomalies presentes].[Defaut_1])) AS CompteDeDefaut_radier_1
SELECT Type_Ouvrage & Id AS Ouvrage, [Annomalies presentes].Localisation, [Annomalies presentes].effluent
FROM [Annomalies presentes]
GROUP BY [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation, [Annomalies presentes].effluent
PIVOT [Annomalies presentes].Defaut_1;

le but est de faire ensuite un UNION de ces deux requêtes Rq1 et Rq2
pour avoir un récapitulatif des défauts rencontré.
le problème c'est que la requête Rq2 n'a pas le même nombre de colonnes que Rq1, systématiquement moins, car la colonne defaut_2 est moins souvent remplie que defaut_1
donc je ne peux pas faire d'union

j'ai vu que je pouvais fixer en dur le nombre de colonnes avec une instruction:

Code :

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

PIVOT [Annomalies presentes].Defaut_1 In ("Racines","infiltration");

Mais ce n'est pas génial, car si j'ajoute des valeurs de défaut possible dans ma table de valeurs [Val-defaut],
ma requête ne sera plus à jour

L'idéal serait de créer ma liste ("Racines","infiltration", ...) à partir de la table [Val-defaut].
La table Val-dafut n'a qu'une colonne, "anomalie", qui contient des chaines

J'ai trouvé un exemple approchant, avec l’utilisation d'une variable, mais j'ai un peu de mal à l'adapter:
developpez.net:pivot-nombre-colonnes-indetermine

Toute idée serait bien venue.

PS:
Apparemment j'aurais du éviter de me mettre dans ce pétrin en évitant de doubler la colonne défaut, mais en me liant a une table des défauts, mais pour ce coup là, c'est un peu tard ...

[vodiem]

Salut gegematic et bienvenu,

Dans cette optique tu dois effectivement créer une requête dynamiquement que tu exécuteras en VBA.
du genre:

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim rs As Recordset
Dim critereIn As String
Set rs = CurrentDb.OpenRecordset("[Val-defaut]")
While Not rs.EOF
   critereIn = critereIn & IIf(critereIn = "", "", ",") & rs![anomalie]
   rs.MoveNext
Wend
 
larequete="TRANSFORM .... PIVOT ... IN (" & critereIn & ")"
DoCmd.RunSQL larequete

cela dit ce qui me parait le plus simple c'est de faire une union avant la requête croisée dynamique.

[gegematic]

Bonjour Vodiem et merci de ta réponse,

Dans cette optique tu dois effectivement créer une requête dynamiquement que tu exécuteras en VBA.

ça me parait clair, je vais essayer de l'appliquer.
Une question, était-ce possible en pur SQL ?

cela dit ce qui me parait le plus simple c'est de faire une union avant la requête croisée dynamique.

ça ce n'est pas possible, puisque qu'au départ je n'ai qu'une table,
en fait je suis obligé de faire 2 requête dynamique de suite, car apparemment ACCESS n'autorise qu'une colonne à la fois comme pivot.

[gegematic]

je ne dois pas en être loin, mais ça ne marche pas encore ...
Voilà la requête SQL qui marche très bien, et que je veux reproduire en VBA:

Code :

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

1
2
3
4
5
6
 
  TRANSFORM (Count([Annomalies presentes].[Defaut_radier_1])) AS CompteDeDefaut_radier_1
SELECT [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation, Type_Ouvrage & Id AS Ouvrage
FROM [Annomalies presentes]
GROUP BY [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation
PIVOT [Annomalies presentes].Defaut_radier_1 IN ("Racines","Raccordement défectueux","Radier dégradé (fissure, cassure)","Abrasion,corrosion","Jonction cunette / banquette non étanche","Infiltration","Absence de radier","Absence de cunette","En charge","Limite de capacité","Stagnation d'effluents");

Voilà mon code VBA :

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 Anno_presentes_Click()
 
	DoCmd.SetWarnings True                           ' message on
	Dim rs As Recordset
	Dim larequete As String ' * 1000
 
	Dim critereIn As String
	Set rs = CurrentDb.OpenRecordset("Val-ano-Radier") '[Val-ano-Radier]
 
While Not rs.EOF
   critereIn = critereIn & IIf(critereIn = "", "", ",") & Chr$(34) & rs![anomalie_radier] & Chr$(34)
   rs.MoveNext
Wend
 
larequete = "TRANSFORM (Count([Annomalies presentes].[Defaut_radier_1])) AS CompteDeDefaut_radier_1"
larequete = larequete & " SELECT [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation, Type_Ouvrage & Id AS Ouvrage"
larequete = larequete & " FROM [Annomalies presentes]"
larequete = larequete & " GROUP BY [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation"
larequete = larequete & " PIVOT [Annomalies presentes].Defaut_radier_1 IN (" & critereIn & ");"
DoCmd.RunSQL larequete
End Sub

Je ne peux pas espionner la chaine au delà de 252 caractères, mais sur le début de 'larequete' et l'ensemble de 'criterIn', tout semble conforme à mon modèle SQL qui marche
malgré tout, j'ai le message d'erreur '2342' exécuter SQl nécessite une instruction SQL
la je sèche ....

[Robert1957]

Bonjour gegematic, vodiem,

Le DoCmd.RunSQL s'applique aux requêtes actions. Ce qui n'est pas le cas ici.

C'est bien expliqué ici: http://www.developpez.net/forums/d67...erreur-2342-a/

Bonne journée

[vodiem]

Salut Robert1957,

Le DoCmd.RunSQL s'applique aux requêtes actions. Ce qui n'est pas le cas ici.

Oui, tu as tout a fait raison !
Il faut créer avec CreateQueryDef ou modifier le sql avec CurrentDB.QueryDefs(...).SQL

Une question, était-ce possible en pur SQL ?

non :

Code :

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

IN(mafonction())

ou

Code :

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

IN(requeteSql)

ne sera pas interprété.

si je ne me trompe pas dans ta structure, avec l'union tu devrais avoir quelque chose comme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
TRANSFORM Count(defaut) AS CompteDeDefaut_radier_1
SELECT Id, Type_Ouvrage, Localisation, effluent
FROM
   (
   SELECT Id, Type_Ouvrage, Localisation, effluent, Defaut_1 as defaut
   FROM [Annomalies presentes]
   UNION ALL
   SELECT Id, Type_Ouvrage, Localisation, effluent, Defaut_2 as defaut
   FROM [Annomalies presentes]
   )
GROUP BY Type_Ouvrage, Id, Localisation, effluent
PIVOT defaut;

[gegematic]

Salut Robert1957,
Oui, tu as tout a fait raison !
Il faut créer avec CreateQueryDef ou modifier le sql avec CurrentDB.QueryDefs(...).SQL

merci a tous les deux, voilà ce que j'ai maintenant qui marche très bien:

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
27
28
29
30
 
Private Sub Anno_presentes_Click()
'DoCmd.SetWarnings False                          ' message off
 
                        DoCmd.SetWarnings True ' message on
                        Dim rs As Recordset
                        Dim rst As Recordset
                        Dim larequete As String ' * 1000
 
Dim critereIn As String
Set rs = CurrentDb.OpenRecordset("Val-ano-Radier") '[Val-ano-Radier]
While Not rs.EOF
   critereIn = critereIn & IIf(critereIn = "", "", ",") & Chr$(34) & rs![anomalie_radier] & Chr$(34)
   rs.MoveNext
Wend
 
larequete = "TRANSFORM (Count([Annomalies presentes].[Defaut_radier_1])) AS CompteDeDefaut_radier_1"
larequete = larequete & " SELECT [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation, Type_Ouvrage & Id AS Ouvrage"
larequete = larequete & " FROM [Annomalies presentes]"
larequete = larequete & " GROUP BY [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation"
larequete = larequete & " PIVOT [Annomalies presentes].Defaut_radier_1 IN (" & critereIn & ");"
'DoCmd.RunSQL larequete: ne marche pas avec une requête de type SELECT: Cela est réservé aux requêtes "actions" (Create, Update, Delete...).
'ci desous la bonne solution
'Set rst = CurrentDb.OpenRecordset(larequete)
'ou celle ci pour visualiser le résultat :
DoCmd.DeleteObject acQuery, "Analyse"    ' supprime la requête Analyse, mais il faut qu'elle existe déjà sinon erreur
 
                CurrentDb.CreateQueryDef "Analyse", larequete  ' crée la nouvelle requete
                DoCmd.OpenQuery "Analyse"
End Sub

si je ne me trompe pas dans ta structure, avec l'union tu devrais avoir quelque chose comme :

Code :

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

TRANSFORM Count(defaut) AS CompteDeDefaut_radier_1 ...

ça m'a l'air génial, si ça marche, je vais gagner du temps !
je teste encore ça avant de marquer résolu ...

[gegematic]

Ok ça marche impec,
Il va me rester un autre sujet, puisque je dois maintenant faire la même chose pour 2 autres familles d'observations et agréger le tout.
mais pour le sujet de départ, c'est parfaitement résolu !
je donne en dessous le code final :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
Private Sub Anno_presentes_Click()
'DoCmd.SetWarnings False                          ' message off
 
                        DoCmd.SetWarnings True ' message on
                        Dim rs As Recordset
                        Dim rst As Recordset
                        Dim larequete As String '
 
Dim critereIn As String
Set rs = CurrentDb.OpenRecordset("Val-ano-Radier") '[Val-ano-Radier]
While Not rs.EOF
   critereIn = critereIn & IIf(critereIn = "", "", ",") & Chr$(34) & rs![anomalie_radier] & Chr$(34)
   rs.MoveNext
Wend
 
larequete = "TRANSFORM Count(Defaut_radier) As CompteDeDefaut_radier"
larequete = larequete & " SELECT Type_Ouvrage & Id AS Ouvrage, [Annomalies presentes].Localisation, [Annomalies presentes].effluent"
'larequete = larequete & " FROM [Annomalies presentes]"
larequete = larequete & " FROM"
larequete = larequete & " (SELECT Id, Type_Ouvrage, Localisation, effluent, Defaut_radier_1 as Defaut_radier"
larequete = larequete & "  FROM [Annomalies presentes]"
larequete = larequete & "  UNION ALL"
larequete = larequete & "  SELECT Id, Type_Ouvrage, Localisation, effluent, Defaut_radier_2 as Defaut_radier"
larequete = larequete & "  FROM [Annomalies presentes])"
larequete = larequete & " GROUP BY [Annomalies presentes].Type_Ouvrage, [Annomalies presentes].Id, [Annomalies presentes].Localisation, [Annomalies presentes].effluent"
larequete = larequete & " PIVOT Defaut_radier IN (" & critereIn & ");"
 
 
 
 
 
'DoCmd.RunSQL larequete: ne marche pas avec une requête de type SELECT: Cela est réservé aux requêtes "actions" (Create, Update, Delete...).
'ci desous la bonne solution
'Set rst = CurrentDb.OpenRecordset(larequete)
'ou celle ci pour visualiser le résultat :
 
FermerRequete ("Analyse_Radier")
SupprimerRequete ("Analyse_Radier")
'DoCmd.DeleteObject acQuery, "Analyse_Radier"    ' supprime la requête Analyse, mais il faut qu'elle existe déjà sinon erreur
 
                CurrentDb.CreateQueryDef "Analyse_Radier", larequete  ' crée la nouvelle requete
                DoCmd.OpenQuery "Analyse_Radier"
End Sub
 
Sub SupprimerRequete(Nom As String)
On Error GoTo err
CurrentDb.QueryDefs.Delete Nom
Exit Sub
err:
If err.Number = 3265 Then MsgBox "Impossible de trouver la requête " & Nom
End Sub
 
Sub FermerRequete(Nom As String)
On Error GoTo err
'CurrentDb.Connection.Close Nom
DoCmd.Close acQuery, Nom
Exit Sub
err:
'MsgBox "La requête " & Nom & n'est pas ouverte
End Sub