Discussion :

[voxov]

Bonjour,

Un sujet "challenging" que l'on peut rencontrer un peu partout: comment calculer des totaux, sous-totaux, sous-sous-totaux, ... en cascade.
Ex:

• comptes, sous comptes, .... en comptabilité

• marque, gamme, modèle, ... en stock

• unité, département, section, service, ... en organisation

• CEO, directeur, chef de service, ... en RH

• ABS, CDO, CDO synthétiques en finance

Particularités:

• ce sont des hiérarchies

• le nombre de niveaux possibles est en principe illimité

• toutes les branches n'ont pas nécessairement la même longueur

• la solution doit être transposable à toute hiérarchie, ce qui exclut toute solution personnalisée

• une solution par itérations (calculer les totaux niveau par niveau) n'est pas admissible, vu sa limitation

Ce qui implique:

• question données: une entité générique avec une relation sur elle-même (self-relationship), ex. en comptabilité: un compte (ex. 61) peut avoir des comptes (ex. 612) qui peuvent avoir des comptes (ex. 6123), etc.

• question traitements: de la récursivité (ex. pour calculer les totaux en cascade):

ex. de récursivité:

Code :

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

1
2
3
4
5
Sub Calculer(Compte)
...
     Call Calculer(Compte)
...
End Sub

Vous, qu'est-ce que vous proposez pour calculer les totaux en cascade dans ce contexte ?


Moi, voici ce que je propose:
1. une procédure qui prépare les données et lance les traitements en cascade (lance la procédure récursive)
2. une procédure récursive qui calcule une somme, en cascade

Je vois 2 techniques pour la procédure récursive:
1. utiliser des recordset pour parcourir l'arborescence (jusqu'aux feuilles de l'arbre), pour calculer la somme, et pour enregistrer le résultat
2. utiliser des recordset pour parcourir l'arborescence, puis une requête SQL pour calculer la somme (select ... sum(...) ... group by ...), et puis une autre pour enregistrer le résultat (update ...)

[marot_r]

Bonjour.

En théorie on peut le faire, VBA est récursif et tu peux donc implanter un parcours d'arbre pour faire ce calcul.
Tes données doivent être dans une table du type :

tblElement
ClefElement
ClefElementParent
ValeurElement

En relation avec elle-même sur ClefElement <-> ClefElementParent.

Dans la pratique c'est simplement invivable, Access n'est pas conçu pour cela.
Il faut tout faire par code et c'est une horreur aussi bien au niveau de l'interrogation des données que de la performance.

Donc je te suggères une solution comptable classique : le nombre de chiffres dans le compte détermine la "profondeur" de l'élément.
Ex : Compte Client - Tous : 51 (Je met n'importe quoi pour le code)
Compte Clients Insolvables : 511
Compte Clients Insolvables XYZ : 5111

Après tu n'as plus qu'a faire des sommes par "préfixe" (ex : Tous les comptes qui commencent par "5", tous les comptes qui commencent par "51" ...

Une autre possibilité, plus lourde est de prévoir une profondeur maximale (ex : 10 niveaux) et de bâtir tes tables en conséquence.

tblElement01
ClefElement

tblElement02
ClefElement02
ClefElement01

Je ne l'ai jamais fait mais je sens que cela doit être pas mal pénible à vivre.

A+

[voxov]

Merci pour cette contribution détaillée.

C'est la formule itérative: calculer les totaux niveau par niveau, du plus bas (ex. les comptes les plus détaillés, ex. 612345) jusqu'au plus haut (la racine de l'arbre, ex. 6).

Il est vrai que l'on peut déterminer le niveau à partir de la longueur du compte (ex. 612345 = niveau 6) et donc le nombre d'itérations nécessaires. Mais cela implique des limitations assez contraignantes dans la codification des comptes. Pas question de codifier un compte p.ex. 440FMICROSOFT.
Et aussi: on n'a pas toujours une codification structurée comme en comptabilité.

Alors pour éviter cela on peut fixer le nombre d'itérations arbitrairement, en prévoyant plus de niveaux que nécessaire. Mais dans certains cas il est difficile de prévoir le nombre de niveaux nécessaires, p.ex.:

• le traçage des produits titrisés en finance: là où l'on s'attendait à une quinzaine d'imbrications; il y a eu des cas jusqu'à 70 niveaux ! (au passage, c'est d'avoir été aveugle sur cette complexité qui a provoqué la crise financière de 2008)

• le traçage des dépendances dans un grand projet: on s'attendait à quelques niveaux de dépendance; on a trouvé des chaines de dépendance de plus de 20 éléments

Et aussi, c'est compliqué à utiliser, parce qu'il faut fixer pour chaque compte le niveau auquel il sera totalisé.
C'est encore plus compliqué quand on fait des changements dans la structure de regroupement, ex. le compte 440FMICROSOFT qui était au 4ème niveau passe au 5ème niveau, et il faut changer le niveau de tous les sous-comptes également. C'est beaucoup plus simple de ne changer que le père du compte; pas besoin de changer les sous-comptes.


Alors la formule la plus souple reste donc selon moi celle qui recourt à du self-relationship et à un traitement récursif.

Pour le self-relationship, c'est bizarre au début, mais on s'y fait très vite.

Pour la récursivité:

• C'est plus difficile d'imaginer comment le traitement va se comporter dans tous les cas de figure. Et déjà qu'au départ, une procédure (ou fonction) qui s'exécute en s'appelant elle-même, ce n'est pas intuitif. Donc, difficile à concevoir et à mettre au point.

Dans la pratique c'est simplement invivable, Access n'est pas conçu pour cela.

• Je présume que le "invivable" vise la difficulté que je viens de commenter.

• Mais je ne vois pas en quoi Access (le VBA) ne serait pas conçu pour cela, ou serait moins conçu qu'un autre pour cela.

• Sur le plan de la performance, le traitement récursif est beaucoup plus rapide qu'un traitement itératif.

• Un traitement récursif est le plus souvent très simple (peu de lignes de code), ce qui contraste avec la difficulté intellectuelle de le développer

J'en reste donc à cette formule et j'en reviens à ma question du départ: quelle est la meilleure façon de calculer des totaux en cascade, quand on utilise une self-relationship pour les données et de la récursivité pour le calcul ?

Les commentaires avisés restent toujours utiles, dans la mesure où ils peuvent présenter un éclairage différent, conforter le point de vue, ou proposer une amélioration.

[marot_r]

Bonjour.

Access est une base de données relationnelle et le moteur qui interprète le SQL et tous les objets existants pour la visualisation des données (formulaire, rapport) ne sont pas conçus pour le récursif.
Oui VBA te permet de faire des appels récursifs mais quand tu utilise du VBA pour accéder aux données tu es sérieusement plus lent que quand tu passes par le SGBD.
Et là il va TOUT falloir faire par code.

A+

[Dermochelys]

Bonjour,

Voxov, je serai curieux de voir le code qui permettrait de faire ce que tu envisages. N'hésite pas à le poster si/quand tu l'auras ... même si j'ai peur de sa complexité

Bonne journée!

[voxov]

Ce n'est pas complexe (je trouve que le code est très simple), c'est juste plus difficile à imaginer à cause de la récursivité.

Ci-dessous le code de la procédure récursive qui calcule le total d'une ligne de regroupement par catégorie dans une table de données (TempResultat).
Les lignes de catégorie ont été ajoutées au préalable dans la table des données et initialisées.
MyidCategorie est l'identifiant de la catégorie
MyidPere est l'identifiant de la catégorie dans laquelle la ligne courante doit être cumulée (self-relationship)

Le calcul des totaux est fait par requête SQL, de même que la mise du total calculé dans le père.
On peut aussi faire ça par recordset. Ou encore autrement ? Qu'est-ce qui est le mieux ? Ce sont les questions de mon post.

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
Sub CalculerUneLigne(MyidCategorie As Long, MyidPere As Long)
'Calculer une ligne du résultat avec récursivité
'Totaux calculés et mis dans le père par des requêtes SQL
 
    Dim MesFils As DAO.Recordset
    Dim MesCumuls As DAO.Recordset
 
'sélectionner les fils de la ligne courante
    MySELECT = "SELECT idCategorie, idPere "
    MyFROM = "FROM TempResultat "
    MyWHERE = "WHERE (idCategorie <> 0) AND (idPere = " & MyidCategorie & ") "
    MyQuery = MySELECT & MyFROM & MyWHERE & ";"
    Set MesFils = CurrentDb.OpenRecordset(MyQuery)
 
    's'il y a des fils, chercher leurs fils
    'prendre leurs fils un par un jusqu'au dernier
    'si un de ces fils a lui-même des fils, refaire le même processus (récursivité)
    'sinon ajouter son montant (du fils) au total (du père)
 
    If MesFils.BOF And MesFils.EOF Then
        'ne rien faire: il n'y a pas de fils
    Else
        MesFils.MoveFirst
        While Not MesFils.EOF
        'pour chacun des fils, aller voir si le fils a lui-même des fils, etc. (récursivité)
            Call CalculerUneLigne(MesFils!idCategorie, MesFils!idPere)
            MesFils.MoveNext
        Wend
 
        'prendre le père et faire le total de tous ses fils et mettre ce total dans le père
        MesFils.MoveFirst
 
    'faire le total des fils
        MyFROM = "FROM TempResultat AS ttt INNER JOIN Categories as aaa ON ttt.IdCategorie = aaa.IdCategorie "        
        MySELECT = "SELECT aaa.idPere, Sum(ttt.SumOperation) "
        MyWHERE = "WHERE (aaa.idPere=" & MesFils!idPere "
        MyGROUPBY = "GROUP BY aaa.idPere "
        MyQuery = MySELECT & MyFROM & MyWHERE & MyGROUPBY & ";"
        Set MesCumuls = CurrentDb.OpenRecordset(MyQuery)
 
    'mettre ce total dans le père
        MesCumuls.MoveFirst
        MyUPDATE = "UPDATE TempResultat AS ttt "
        MySET = "SET ttt.SumOperation = '" & MesCumuls(1) & " "
        MyWHERE = "WHERE (ttt.IdCategorie=" & MesFils!idPere & ")  "
        MyQuery = MyUPDATE & MySET & MyWHERE & ";"
        DoCmd.RunSQL MyQuery
 
        MesCumuls.Close
        Set MesCumuls = Nothing
    End If
 
    MesFils.Close
    Set MesFils = Nothing
 
End Sub

Feedback svp.