Discussion :

[Bonero]

bonjour a tous,
j’espere trouver la solution a ce probelme qui me tracasse depuis deux jours.
TableA contient en fait le ID des vendeurs . TableB contient les vendeurs(ID) ainsi que les differentes categories de produits vendus au cours des semestres A1 et A2. En fait, il y a plusieurs annees, mais je mets juste 2 pour faire simple.
Pour chaque vendeur de la table TableA, j’aimerais lister le nombre de DIFFERENTES categories vendues; j’insiste sur les termes DIFFERENTES categories et sur le fait que je veuille utilise TableA dans ma requete. En fait , il s’agit d’un probleme avec COUNT DISTINCT que Access ne supporte pas.

Code :

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

1
2
3
4
5
TableA
ID
1
2
3

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
TableB
ID    A1    A2
1    a    a
1    a    a
1    b    a
2    c    c
2    c    cc
3    d    d
3    d    d
3    e    d
3    e    d
4    f    f
4    f    f

Le resultat escompte sera alors:

Code :

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

1
2
3
4
ID    DistinctCountS1    DistinctCountS2
1    2    1
2    1    2
3    2    1

Merci de votre aide.

[f-leb]

Salut,

tu peux faire un:

Code sql :

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

1
2
3
SELECT ID, A1 as categorie, "semestre 1" as semestre FROM TableB
UNION
SELECT ID, A2, "semestre 2" FROM TableB

Code :

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

1
2
3
4
5
6
7
8
9
10
11
TableB
ID	Catg	semestre
1      a       "semestre 1"
1      b       "semestre 1"
1      a       "semestre 2"
2      c       "semestre 1"
2      c       "semestre 2"
2      cc      "semestre 2"
3      d       "semestre 1"
3      d       "semestre 2"
3      e       "semestre 1"

en regroupant les enregistrements pas semestre et par ID, on devrait obtenir le bon compte.

[philben]

bonjour,

La requête :

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
 
SELECT tablea.id,
       r1.ca1,
       r2.ca2
FROM   tablea
       INNER JOIN ((SELECT id,
                          Count(a1) AS ca1
                   FROM   (SELECT DISTINCT id,
                                           a1
                           FROM   tableb) AS [%$##@_Alias]
                   GROUP  BY id) AS r1
                   INNER JOIN (SELECT id,
                                      Count(a2) AS ca2
                               FROM   (SELECT DISTINCT id,
                                                       a2
                                       FROM   tableb) AS [%$##@_Alias]
                               GROUP  BY id) AS r2
                           ON r1.id = r2.id)
               ON tablea.id = r1.id;

Quant à la solution de f-leb, elle n'est pas correcte sur deux points :

• Elle ne répond pas à ce que tu souhaites
• Elle ne respecte pas les formes normales

L'alternative est la suivante :

1. Créer la TableC des semestres (NumSem, Designation)
2. Créer la TableB avec deux clef étrangères (Id et NumSem) et une colonne Cat
3. Renseigner la tableB :

Code :

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

1
2
3
4
5
6
7
8
9
 
Id NumSem Cat
1      1       a
1      1       a
1      1       b
1      2       a
1      2       a
1      2       a
...

Continuer pour les autres Id.

Enfin, créer une requête d'analyse croisée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
TRANSFORM Count(tableB.cat) AS CompteCat
SELECT tableA.id
FROM   tableA
       INNER JOIN (SELECT DISTINCT id,
                                    [numsem],
                                   cat
                        FROM   tableB) AS R
               ON tableA.id = R.id
GROUP  BY tableA.id
PIVOT r.numsem;

Bonne continuation

Philippe

[f-leb]

à part la tableA que j'ai oubliée dans la bataille, la requête UNION retourne bien de quoi faire le comptage il me semble.

En regroupant, comme je l'ai dit, pas semestre et par ID, on obtient le bon compte. Il faut alors basculer en Analyse Croisée:

Code sql :

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

1
2
3
4
5
TRANSFORM Count(*) AS Nbr
SELECT ID
FROM RequeteUnion
GROUP BY ID
PIVOT semestre;

et j'obtiens ça:

Le resultat escompte sera alors:

Code :

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

1
2
3
4
ID    DistinctCountS1    DistinctCountS2
1    2    1
2    1    2
3    2    1

[philben]

Hmmm...
Tous les chemins mènent à Rome, même les plus escarpés...

Ca me fait penser que je n'ai pas parlé de la tableC.
Il faut la renseigner ainsi :

NumSem, Designation
1, Semestre 1
2, Semestre 2

On l'utilise ensuite dans la requête d'analyse croisée pour afficher 'Semestre 1' et 'Semestre 2' en-tête des colonnes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
TRANSFORM Count(tableB.cat) AS comptecat
SELECT tableA.id
FROM   (tableA
        INNER JOIN (SELECT DISTINCT id,
                                    [numsem],
                                    cat
                    FROM   tableB) AS R
                ON tableA.id = R.id)
       INNER JOIN tableC
               ON R.numsem = tablec.numsem
GROUP  BY tableA.id
PIVOT tableC.designation;

Philippe

[Bonero]

Bonsoir a tous,
Grand merci pour vos reponses les unes plus elegantes que les autres.
Cependant, je constate que le code devient tres vite long et difficile a maintenir si j'ai plusieurs semestres/annees.
Partant ce constat, j’ai essaye de bidouiller un UDF (user defined function) qui a pour mission de calculer le CountDistinct que nous cherchons tous a repliquer en Access.

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
Function fctCountDistinct(strFn As String, numID As Variant)
 'Role: CountDistinct pour le champs dans lequel il est appele.
 
      'strFn : est cense etre le nom du champs des semestres c-a-d TableB.A1, TableB.A2, TableB.A3, ...TableB.An
      'numID : doit etre TableA.[ID]
 
      On Error GoTo fct_Error
 
      Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
      Dim strSQL As String
 
      Set Cn = Application.CurrentProject.Connection
      Set Rs = CreateObject("adodb.recordset")
 
       strSQL = " Select Count(*) From ( Select Distinct [" & strFn & "] From TableB Where [TableB].[ID]  =  " & numID & ")"
      Debug.Print strSQL
      With Rs
            .Source = strSQL
            .ActiveConnection = Cn
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
 
            .Open , , , , adCmdText
    End With
 
    While Rs.EOF <> True
            fctCountDistinct = Rs.Fields(0).Value
            Rs.MoveNext
    Wend
 
      Rs.Close
      Set Rs = Nothing
 
fct_Error:
      Exit Function
      MsgBox "..fct Exit ...."
End Function

Cette fonction va etre alors tout simplement appelee dans l’editeur de requete de la facon suivante:

Code :

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

1
2
3
4
5
6
7
SELECT TableA.ID
, fctCountDistinct([TableB].[A1],[TableA].[ID]) AS CompteA1
, fctCountDistinct([TableB].[A2],[TableA].[ID]) AS CompteA2
FROM TableA INNER JOIN TableB ON TableA.ID = TableB.ID
GROUP BY TableA.ID
, fctCountDistinct([TableB].[A1],[TableA].[ID])
, fctCountDistinct([TableB].[A2],[TableA].[ID]);

Le probleme que je rencontre est que je n’arrive pas a faire passer a ma fonction le nom champs(A1 et A2) comme variable. La variable strFn est donc consideree par la fonction comme une valeur plutot que le nom du champs.
Un petit coup de pouce svp.

[philben]

Ta solution est intéressante mais horriblement lente pour une grosse table...

Pour résoudre ton problème il faut simplement mettre entre guillemets le nom de la colonne pour passer son nom et non sa valeur.

dans ton cas il faut passer : "a1", id

Bonne continuation

Philippe

[Bonero]

bonjour PhilBen,
les mots me manquent pour te remercier. j'ai passe plus de 3 heures pour regler ce probleme alors qu 'il suffisait de placer l'arg entre "..."