[AC-2002] Fonction requête récursive VBA [Résolu]

[jobe3141]

Bonjour à tous,

Je me tourne vers vous, car je rencontre quelques difficultés dans la prog en VBA. Voici mon probleme:
Je dispose d'une table de lien pere fils qui se presente de la forme suivante:

pere Fils

toto tata
toto titi
toto tete
tata sasa
tata soso
titi jojo
soso fufu

et je souhaiterais arriver au résultat suivant:

niv0 niv1 niv2 niv3

toto tata sasa
toto tata soso
toto tata soso fufu
toto titi jojo
toto tete

J'ai dans un premier temps réussi à arriver au résultat final via assistant de création de requetes d'acces mais j 'aurai besoin transcrire l 'ensemble de ces requetes en VBA dans une fonction récursive.

Merci pour l'aide que vous pourrez m'apportez

[jpcheck]

salut,

si l'assistant t'a permis d'avoir le bon resultat, serait-il possible de voir le contenu SQL de la requete generee stp ?

As-tu deja une idee de la recursivite a mettre en place ?

[jobe3141]

Merci de t intéresser à mon problème. Pour arriver au résultat final j'ai créer douze requetes qui sont dépendantes l'une de l'autre.
Je commence tous d'abords par la tête de hiérarchie (il n'y en à qu'une) et ensuite je crée autant de requetes que de niveaux en sachant que j'ai 10 niveaux

Voici les trois premieres et tu comprendras le principe:

Code :

select pere as [niveau0], fils as [niveau1] from lien where pere = "Aliment";

(1ere requete enregistrée sous le nom 'req1')

Code :

select niveau0, niveau1, fils as [niveau2] from req1 left join lien on niveau1 = pere;

(2eme requete enregistrée sous le nom 'req2')

Code :

select niveau0, niveau1, niveau2, fils as [niveau3] from req2 left join lien on niveau2 = pere;

(3eme requete enregistrée sous le nom 'req3')

[ClaudeLELOUP]

Bonjour jobe3141 et JP,

Je ne parviens pas à deviner la question en lisant la réponse.

- S’agit-il de donner toutes les lignes des générations en partant de l’ancêtre ?

Alors pourquoi : toto tata soso et toto tata soso fufu ?

-S’agit-il de donner chacun avec ses ancêtres ?

Alors pourquoi : toto tata n’est pas dans la liste ?

[jobe3141]

Bonjour ClaudeLELOUP

Pour répondre à ta question c'est effectivement: Donner toutes les lignes des générations en partant de l’ancêtre.
Merci de m'avoir fait remarquer qu'une erreur s'est glissé dans mon premier post. La ligne toto tata soso ne devrait pas y être.

Merci à tous de l'attention que vous portez à mon PB.

[tedo01]

Bonjour,
Je ne sais pas si ça répond exactement à la demande, mais il est possible de faire une seule requête directement avec par exemple les 10 niveaux, en prenant la table plusieurs fois avec des alias différents :

Code :

1
2
SELECT N0.Pere, N0.Fils, N1.Fils, N2.Fils, N3.Fils, N4.Fils, N5.Fils
FROM (((((((((T_Lien AS N0 LEFT JOIN T_Lien AS N1 ON N0.Fils = N1.Pere) LEFT JOIN T_Lien AS N2 ON N1.Fils = N2.Pere) LEFT JOIN T_Lien AS N3 ON N2.Fils = N3.Pere) LEFT JOIN T_Lien AS N4 ON N3.Fils = N4.Pere) LEFT JOIN T_Lien AS N5 ON N4.Fils = N5.Pere) LEFT JOIN T_Lien AS N6 ON N5.Fils = N6.Pere) LEFT JOIN T_Lien AS N7 ON N6.Fils = N7.Pere) LEFT JOIN T_Lien AS N8 ON N7.Fils = N8.Pere) LEFT JOIN T_Lien AS N9 ON N8.Fils = N9.Pere) LEFT JOIN T_Lien AS N10 ON N9.Fils = N10.Pere;

[ClaudeLELOUP]

Vois si ceci correspond à ton souhait

Dans un module : ce code

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
Option Compare Database
Option Explicit
 
 
Public Function Pere(Descendant As String) As String
Pere = Nz(DLookup("pere", "Filiations", "fils=""" & Descendant & """"), "")
End Function
Public Function Fils(Ascendant As String) As String
Fils = Nz(DLookup("Fils", "Filiations", "pere=""" & Ascendant & """"), "")
End Function
 
Public Function Lignee(Individu As String) As String
Dim Ascendant As String, Descendant As String, Tous() As String
Lignee = Individu
Descendant = Individu
Do
  Ascendant = Pere(Descendant)
     If Ascendant <> "" Then
        Lignee = Ascendant & "|" & Lignee
        Descendant = Ascendant
     Else
        Exit Do
     End If
Loop
' on retient si le premier n'a pas de père et si le dernier n'a pas de fils
Tous = Split(Lignee, "|")
If Pere(Tous(0)) <> "" Or Fils(Tous(UBound(Tous))) <> "" Then Lignee = "": Exit Function
'If Fils(Tous(UBound(Tous))) <> "" Then Lignee = "": Exit Function
End Function

La requête qui te donne la liste :

Code :

1
2
3
SELECT replace(Lignee([fils]),"|","  ") AS Lignée
FROM Filiations
WHERE (((replace(Lignee([fils]),"|","  "))<>""));

A toutes fins utiles, mon test en annexe.

Je reviens volontiers pour expliquer si utile.

[jobe3141]

Merci bcp a tous et surtout à claudeleloup pour sa réponse.
Claudeleloup je t'avoue que je ne comprend pas grand chose au module et ta solution répond en partie à ce que je cherche à faire. En fait je souhaiterai que chacun des fils soient dans un champ et non dans un même champ. Je te propose de trouver ci-joint la base que tu m'as envoyé avec en plus une table dans laquelle le résultat souhaité.
Serait il également possible d'inclure des claure Where pour afficher les fils en fonction d'un critére?

J'espère avoir été assez clair et précis
Merci pour tout.

[ClaudeLELOUP]

Le nombre de niveaux est-il de 4 maximum ou est-ce le hasard de ton exemple ?

[ClaudeLELOUP]

J'ai fait l'impasse sur 4 générations maximum.

Voici, je crois.

J'ai mis des commentaires dans le code pour t'aider à comprendre ce qui s'y passe.
J'ignore ton niveau de connaissance du code.
N'hésite pas à revenir. Si nécessaire, je développerai pas à pas.

[ClaudeLELOUP]

Bonjour,

Je résume : pour chaque dernier de lignée, tu veux la liste de ses ascendants.

1re étape
Pour reconnaître qu’il s’agit d’un dernier de lignée, on va vérifier qu’il n’a pas de fils.

Voici une fonction qui donne le nom d’un fils (le premier au hasard) pour un individu donné en paramètre :

Code :

1
2
3
Public Function Fils(Ascendant As String) As String
Fils = Nz(DLookup("Fils", "Filiations", "pere=""" & Ascendant & """"), "")
End Function

Cette fonction renvoie « vide » si l’individu n’a pas de fils. Dans ce cas, l’individu est donc un dernier de lignée.

2e étape
Pour construire la filiation, il faut connaître le père de chacun.

Voici la fonction

Code :

1
2
3
Public Function Pere(Descendant As String) As String
Pere = Nz(DLookup("pere", "Filiations", "fils=""" & Descendant & """"), "")
End Function

3e étape
Pour chaque dernier de lignée, on va utiliser la fonction pere en boucle. On cherche d’abord le père du dernier de lignée, ensuite le grand-père… jusqu’à ce que la fonction renvoie « vide ». C’est alors le patriarche. Fin de la boucle.
Voici une fonction qui fait le travail. Au fur et à mesure, les noms des membres sont notés de la droite vers la gauche et séparés par le signe « | » (Alt +124).
Ce signe « rare » est choisi comme séparateur parce qu’on est sûr qu’aucun nom ne le contient.

Voici la fonction

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Function Lignee(Individu As String) As String
Dim Ascendant As String, Descendant As String, Tous() As String
Lignee = Individu
Descendant = Individu
Do
  Ascendant = Pere(Descendant)
     If Ascendant <> "" Then
        Lignee = Ascendant & "|" & Lignee
        Descendant = Ascendant
     Else
        Exit Do
     End If
Loop
' on retient si le premier n'a pas de père et si le dernier n'a pas de fils
Tous = Split(Lignee, "|")
If Pere(Tous(0)) <> "" Or Fils(Tous(UBound(Tous))) <> "" Then Lignee = "": Exit Function
'If Fils(Tous(UBound(Tous))) <> "" Then Lignee = "": Exit Function
End Function

4e étape
Pour présenter le résultat comme tu le souhaites, il « suffit » de décomposer une lignée et de loger chaque membre dans le champ de la table qui correspond à son niveau.

Voici la routine qui fait le travail. Elle est associée au clic sur le bouton.

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
Public Sub CreerLaTable()
Dim rst As Recordset, Individu() As String, sSql As String
DoCmd.SetWarnings False
'purger la table Resultat
DoCmd.RunSQL ("DELETE Resultat.niv0 FROM Resultat;")
'Lire le contenu de la table filiations du début à la fin
Set rst = CurrentDb.OpenRecordset("filiations")
Do Until rst.EOF
   If Lignee(rst("fils")) <> "" Then    'si c'est une lignée on continue, si non au suivant
        ' on crée un tableau avec chaque valeur
       Individu = Split(Lignee(rst("fils")), "|")
        ' on construit le sql d'une requête ajout (modulée selon le nombre d'éléments)
       sSql = "INSERT INTO Resultat ( niv0, niv1"
       If UBound(Individu) >= 2 Then sSql = sSql & ", niv2"
       If UBound(Individu) >= 3 Then sSql = sSql & ", niv3"
       sSql = sSql & " ) " _
       & "SELECT """ & Individu(0) & """ AS Expr0, """ _
                        & Individu(1) & """ AS Expr1"
       If UBound(Individu) >= 2 Then sSql = sSql & ", """ & Individu(2) & """ AS Expr2"
       If UBound(Individu) >= 3 Then sSql = sSql & ", """ & Individu(3) & """ AS Expr3"
       sSql = sSql & ";"
        ' on exécute la requête ajout pour cette lignée
       DoCmd.RunSQL (sSql)
   End If
       ' on lit le suivant
   rst.MoveNext
Loop
DoCmd.SetWarnings True
' un message de bonne arrivée
MsgBox "la table est créée, tu peux vérifier" & vbLf & " A la prochaine."
End Sub

Pour t’aider à comprendre la syntaxe : dans le code, tu places ton curseur à l’intérieur d’un mot et tu enfonces F1 ---> l’aide Access s’affiche à la bonne page. Si cela ne suffit pas, reviens.

Au plaisir de te recroiser.

[jobe3141]

Mon PB est résolut grâce a ClaudeLeloup. Merci bcp à toi. J'ai fait comme tu m'as dit en consultant l'aide j'ai fini par comprendre ça a mis du temps mais j'y suis arrivé.

Merci encore