[AC-2003] Lister les elements d’un groupe dans une requete: [Résolu]

[Bonero]

Bonjour tout le monde,

Je dois realiser un rapport base sur la table ci dessous mais je bloque sur la derniere etape.

Categorie Produits SumOfVentes NomCreateur
A Naval 9 kelly
A transport 5 alain
A transport 4 Bonero
A transport 1 david
B Construction 3 albert
B Construction 7 jojo
B Construction 2 yvette
B finance 3 divine
B finance 6 roger



J’ai reussi a grouper par categorie, par Produits, faire la somme des ventes au niveau de chaque Produits, compter les Vendeur pour chaque Produits.
Jusque la tout va bien. Le code utilise et le resultat obtenu sont en bas:

Code :

1
2
3
4
5
6
7
8
 
SELECT MaTable.Categorie
, MaTable.Produits
, Sum(MaTable.Ventes) AS SumOfVentes
, Count(MaTable.NomVendeur) AS CountOfNomVendeur
FROM MaTable
GROUP BY MaTable.Categorie
, MaTable.Produits;

Categorie Produits SumOfVentes CountOfNomVendeur
A Naval 9 1
A transport 10 3
B Construction 12 3
B finance 9 2


Derniere Etape qui me pose probleme:
J’aimerais inclure dans dans le resultat ci-dessus une derniere colonne qui listerait le nom et la part (contribution) de chaque Vendeur dans chaque Produit.
Le resulat final ressemblera au tableau ci dessous:

Categorie Produits SumOfVentes CountOfNomVendeur NomVendeur-Contribution
A Naval 9 1 Kelly-9
A transport 10 3 Alain-5,David-1, Bonero-4
B Construction 12 3 Albert-3, yvette-2, jojo-7
B finance 9 2 roger-6, divine-3




Merci de m’aider.



ps: j ai affiche un fichier excel qui contient les tables ci dessus vu que le contenus des tables sont distordus dans le corps de message.
merci

[dumas.blr]

Bonjour Bonero,

A mon avis, il faut faire le SQL en 3 passes
1ere passe : ta requête actuelle
2ème passe : ta contribution par vendeur

Code :

1
2
3
4
5
6
7
8
SELECT MaTable.Categorie
, MaTable.Produits
, MaTable.NomVendeur
, Sum(MaTable.Ventes) AS SumOfVentes
FROM MaTable
GROUP BY MaTable.Categorie
, MaTable.Produits
, MaTable.NomVendeur;

3ème passe (et peut-être 4ème, je n'ai as vérifiée) :
requête tableau croisé entre des 2 requêtes avec Jointure sur categorie, produit et pivot sur nomvendeur

[dumas.blr]

J'ai fait ta requête en test chez moi
Regarde si cela te convient

1ere requête

Code :

1
2
3
SELECT Matable.Categorie, Matable.Produits, Sum(Matable.Ventes) AS SommeDeVentes, Count(Matable.NomVendeur) AS CompteDeNomVendeur
FROM Matable
GROUP BY Matable.Categorie, Matable.Produits;

2ème requête

Code :

1
2
3
SELECT Matable.Categorie, Matable.Produits, Matable.NomVendeur, Sum(Matable.Ventes) AS SommeDeVentes
FROM Matable
GROUP BY Matable.Categorie, Matable.Produits, Matable.NomVendeur;

3ème requete

Code :

1
2
SELECT Requête1.Categorie, Requête1.Produits, Requête1.SommeDeVentes, Requête1.CompteDeNomVendeur, Requête2.NomVendeur, Requête2.SommeDeVentes
FROM Requête1 INNER JOIN Requête2 ON (Requête1.Categorie=Requête2.Categorie) AND (Requête1.Produits=Requête2.Produits);

4ème requête

Code :

1
2
3
4
5
TRANSFORM Sum([Requête2].[SommeDeVentes]) AS Expr1
SELECT Requête5.[Categorie], Requête5.[Produits], Requête5.Requête1.SommeDeVentes, Requête5.CompteDeNomVendeur
FROM Requête5
GROUP BY Requête5.[Categorie], Requête5.[Produits], Requête5.Requête1.SommeDeVentes, Requête5.CompteDeNomVendeur
PIVOT Requête5.[NomVendeur];

[Bonero]

Bonjour Dumas,

Merci de m’avoir repondu mais ce n’est pas ce que je voulais.
Je reexplique la derniere etape sur laquelle je bloque. Le resultat final doit comporter 5 colonnes:
Categorie, Produits, SumOfVentes, CountOfVendeur ET FINALLEMENT la colonne NomVendeur-Contribution. Cette derniere colonne sera composee de NomVendeur “espace” Ventes ( ie les ventes du NonVendeur).

Pour etre plus explicite, voici le contenu de la derniere (ie 5ieme) colonne:

Ligne 1 de la colonne 5:
Kelly 9
' c-a-d tous ces elements doivent etre dans une meme "cellule"


Ligne 2 de la colonne 5:
Alain 5, David 1, Bonero 4
' c-a-d tous ces elements doivent etre dans une meme "cellule"


Ligne 3 de la colonne 5:
Albert 3, yvette 2, jojo 7
'c-a-d tous ces elements doivent etre dans une meme "cellule"



Ligne 4 de la colonne 5:
roger 6, divine 3
'c-a-d tous ces elements doivent etre dans une meme "cellule"



merci de ton soutien.

[dumas.blr]

Bonjour Bonero,

Pour ton besoin, tu sors des utilisations habituelles du SQL, ce qui te demandera un traitement complexe.
En SQL natif, le traitement risque d'être très lourd et pas forcément maintenable, car il te faudra utiliser des notions évoluées de SQL, qui ne sont pas forcément transposables avec Access (le notion de RANK, etc..)

La solution la moins complexe à mes yeux serait de combiner des requêtes SQL avec un programme écrit en VBA utilisant DAO ou ADO. Si les notions de recordset et de programmation structurées ne te font pas peur, c'est sur cette voie que je te conseillerais de t'engager.

Le principe :

1. garder la requête 1 telle quelle
2. utiliser la requête 2 en entrée du programme VBA
3. dans le programme VBA, faire les concaténations d'enregistrement en fonction des clés d'identification à partir du recordset issu de la requête, et créér une table temporaire qui aura la même structure que la la requête 1
4. creer une requête 3 qui fera la jointure entre la requete 1 et la table temporaire.

Voila, je ne pense pas que l'on puisse faire plus simple

[Bonero]

salut dumas,
merci d avoir repondu. je sais bidouiller quelques proprietes de l'objet recordset en ADO. je vais essayer cette suggestion ce soir et je te dirai quoi.
je savais des le depart que mon besoin n etait un truc simple a realiser. mais n hesite pas a me faire signe si entretemps tu as d'autres pistes a me suggerer.

merci

[dumas.blr]

Si tu programmes correctement ta procédure, tu devrais même pouvoir l'utiliser directement dans ta requête; c'est une des grandes force d'Access de pouvoir encapsuler une built-in fonction dans du code SQL.
Je ne suis pas sur que cela puisse marcher avec un objet table en sortie, mais ça vaudrait le coup d'essayer

[dumas.blr]

pour t'aider, regarde aussi ce topic

http://access.developpez.com/sources...QLLigneColonne

[Bonero]

merci pour ce tuto,
je m en servirai comme point de depart. le premier sujet #Concaténer plusieurs enregistrements dans une seule colonne
# a l 'air de repondre a ma question. je vais le modifier et l'implementer en ADO.

[Bonero]

Bonjour Dumas,
J’ai legerement modifie le code du tuto pour obtenir la function ci dessous.

Code :

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
Function RecupListe(xProduits As String) As String
 
 
    Dim Cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
 
    Set Cn = Application.CurrentProject.Connection
    Set rs = CreateObject("adodb.recordset")
 
    strSQL = "Select NomVendeur, Ventes From Matable Where Produits = " & Chr(34) & xProduits & Chr(34)
 
    With rs
        .Source = strSQL
        .ActiveConnection = Cn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
 
        .Open , , , , adCmdText
    End With
 
    While rs.EOF <> True
        RecupListe = RecupListe & rs.Fields(0).Value & " " & rs.Fields(1).Value & ", "
        rs.MoveNext
    Wend
 
    RecupListe = Left(RecupListe, Len(RecupListe) - 2)
 
    rs.Close
    Set rs = Nothing
 
End Function

J’ai integre la function le QueryDesign et j’ai egalement obtenu le result escompte.
Le code SQL derriere le QueryDesign est le suivant:

Code :

1
2
3
4
5
6
7
SELECT Matable.Categorie
, Matable.Produits
, Sum(Matable.Ventes) AS SommeDeVentes
, Count(Matable.NomVendeur) AS CompteDeNomVendeur
FROM Matable
GROUP BY Matable.Categorie
, Matable.Produits;

Maintenant, je me demande comment appeler cette fonction dans la fenetre VBA (cad dans une procedure ADO) et obtenir le meme resultat dans un objet Recordset.

Merci de me filer le dernier coup de main. Je suis vraiment a mes debuts en programmation VBA(access).

[dumas.blr]

Bonjour Bonero,

Je voudrais m'assurer que j'ai bien compris :

Souhaites tu intégrer la fonction RecupListe dans la requête que tu as créée avec l'éditeur de requête, ou bien changer l'origine du SQL dans ta fonction Recupliste ?

As-tu pu tester ta fonction RecupListe et vérifier qu'elle marchait bien ?

[Bonero]

Envoyé par dumas.blr

Bonjour Bonero,

Je voudrais m'assurer que j'ai bien compris :

Souhaites tu intégrer la fonction RecupListe dans la requête que tu as créée avec l'éditeur de requête, ou bien changer l'origine du SQL dans ta fonction Recupliste ?

As-tu pu tester ta fonction RecupListe et vérifier qu'elle marchait bien ?

bonjour,
j'ai deja reussi a integrer la fonction RecupListe dans une requete creee dans l'editeur de requete. j avais eprouve qlq difficultes dans lorsque jai voulu appeler la mme fonction dans un module de vba. maintenant, c'est regle; jai trouve la solution.

grand merci pour ton aide.

[Bonero]

Envoyé par dumas.blr

Bonjour Bonero,

Je voudrais m'assurer que j'ai bien compris :

Souhaites tu intégrer la fonction RecupListe dans la requête que tu as créée avec l'éditeur de requête, ou bien changer l'origine du SQL dans ta fonction Recupliste ?

As-tu pu tester ta fonction RecupListe et vérifier qu'elle marchait bien ?

le tuto que tu m as conseille m a beaucoup aide.