Developpez.com

Une très vaste base de connaissances en informatique avec
plus de 100 FAQ et 10 000 réponses à vos questions

Classification des données et structures arborescentes dans Access

Objectif : Comment traiter dans Microsoft Access les problèmes de classification des données et de structures arborescentes au moyen de requêtes et de fonctions VBA.

Niveau requis : avancé.

Commentez cet article : 2 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Nombreux sont les exemples dans lesquels on a recours à des classifications de données, simples ou plus complexes avec l'utilisation de structures arborescentes. Voici une liste de cas concrets :

  • comment classer des articles par groupes, familles et sous-familles ;
  • comment afficher une classification du règne animal ;
  • comment générer un arbre généalogique.

L'objectif de cet article est, après avoir défini ces différents concepts, de présenter des exemples de requêtes et de code VBA qui permettent de générer ces structures. Pour une meilleure compréhension des choses, on évitera de rentrer trop dans le détail.

II. Classification simple de données

Classification : action de ranger par classes, par catégories des choses présentant des critères en commun.

II-A. Classement d'articles

Problématique

Disposant d'articles possédant des caractéristiques communes, on souhaite les classer par groupes, familles et sous-familles.

Hiérarchie

Niveau 

Type d'ensembles 

Références 

Groupes 

A, B, C.. 

Familles 

A001, A002.., B001, B002.., C001.. 

Sous-familles 

A001.001, A001.002.., B001.001, B001.002.. 

Articles 

A001.001.001, A001.001.002.., B001.001.001, B001.001.002.. 

II-A-1. Tables nécessaires

T_GroupeArticle

Nom du champ 

Type du champ 

Description 

IDGroupeArticle 

Entier long 

Identifiant du groupe 

RefGroupeArticle 

Texte 

Référence du groupe  : A, B, C

DesignationGroupeArticle 

Texte 

Désignation du groupe 

T_FamilleArticle

Nom du champ 

Type du champ 

Description 

IDFamilleArticle 

Entier long 

Identifiant de la famille 

RefFamilleArticle 

Texte 

Référence de la famille  : A001, A002, A003

DesignationFamilleArticle 

Texte 

Désignation de la famille 

T_SousFamilleArticle

Nom du champ 

Type du champ 

Description 

IDSousFamilleArticle 

Entier long 

Identifiant de la sous-famille 

RefSousFamilleArticle 

Texte 

Référence de la sous-famille  : A001.001, A001.002, A001.003

DesignationSousFamilleArticle 

Texte 

Désignation de la sous-famille 

T_Article

Nom du champ 

Type du champ 

Description 

IDArticle 

Entier long 

Identifiant de l'article 

RefArticle 

Texte 

Référence de l'article 

DesignationArticle 

Texte 

Désignation de l'article 

IDGroupeArticle 

Entier long 

Clé étrangère liée à l'identifiant du groupe d'articles 

IDFamilleArticle 

Entier long 

Clé étrangère liée à l'identifiant de la famille d'articles 

IDSousFamilleArticle 

Entier long 

Clé étrangère liée à l'identifiant de la sous-famille d'articles 

On peut également relier les tables T_GroupeArticle, T_FamilleArticle et T_SousFamilleArticle par des champs de liaison, mais cette structure a l'avantage d'être plus souple et plus simple au niveau de la saisie des données et de la création des requêtes.

II-A-2. Requête de classification des articles

Cette requête réalise la jointure entre les données de la table T_Article, et celles des tables T_Groupe, T_Famille et T_SousFamille.

R_ClassificationArticles
Sélectionnez
SELECT T_Article.IdArticle, T_Article.RefArticle, T_Article.DesignationArticle, T_GroupeArticle.DesignationGroupeArticle AS GroupeArticle, T_FamilleArticle.DesignationFamilleArticle AS FamilleArticle, T_SousFamilleArticle.DesignationSousFamilleArticle AS SousFamilleArticle
FROM ((T_Article INNER JOIN T_GroupeArticle ON T_Article.GroupeArticle = T_GroupeArticle.IDGroupeArticle) INNER JOIN T_FamilleArticle ON T_Article.FamilleArticle = T_FamilleArticle.IDFamilleArticle) INNER JOIN T_SousFamilleArticle ON T_Article.SousFamilleArticle = T_SousFamilleArticle.IDSousFamilleArticle
ORDER BY T_GroupeArticle.RefGroupeArticle, T_FamilleArticle.RefFamilleArticle, T_SousFamilleArticle.RefSousFamilleArticle, T_Article.IdArticle;

II-A-3. Affichage de résultats

Liste d'articles classés par groupe, famille et sous-famille.

R_ClassificationArticles

IdArticle

RefArticle

DesignationArticle

GroupeArticle

FamilleArticle

SousFamilleArticle

1

A001.001.001

Plaque de plâtre BA 13 standard - 2,00 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 Standard

2

A001.001.002

Plaque de plâtre BA 13 standard - 2,40 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 Standard

3

A001.001.003

Plaque de plâtre BA 13 standard - 2,50 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 Standard

4

A001.002.001

Plaque de plâtre BA 13 hydrofuge - 2,60 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 hydrofuge

5

A001.002.002

Plaque de plâtre BA 13 hydrofuge - 2,70 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 hydrofuge

6

A001.002.003

Plaque de plâtre BA 13 hydrofuge - 2,80 x 1,20

Plaques et cloisons

Plaque de plâtre BA 13

BA 13 hydrofuge

7

A001.003.001

Plaque de plâtre BA 15 standard - 3,00 x 1,20

Plaques et cloisons

Plaque de plâtre BA 15

BA 15 Standard

8

A001.003.002

Plaque de plâtre BA 15 standard - 3,20 x 1,20

Plaques et cloisons

Plaque de plâtre BA 15

BA 15 Standard

9

A001.003.003

Plaque de plâtre BA 15 standard - 3,60 x 1,20

Plaques et cloisons

Plaque de plâtre BA 15

BA 15 Standard

..

..

..

..

..

..

III. Structures arborescentes

En informatique, cette notion désigne souvent celle d'arbre de la théorie des graphes. Une arborescence désigne alors généralement une organisation des données en mémoire, de manière logique et hiérarchisée utilisant une structure algorithmique d'arbre. Cette organisation rend plus efficaces la consultation et la manipulation des données stockées.

C'est le cas par exemple quand on souhaite afficher les différents niveaux d'une classification du règne animal ou d'un arbre généalogique.

III-A. Classification du règne animal

Image non disponible
classification des animaux

Pour créer cette structure, on a besoin d'enregistrer dans une table T_ClasseAnimale les liens entre ces différentes classes.

III-A-1. Table T_ClasseAnimale

T_ClasseAnimale

Nom du champ 

Type du champ 

Description 

IdClasse 

Entier long 

Identifiant de la classe animale 

NomClasse 

Texte 

Nom de la classe 

IDClasseAppartenance 

Entier long

Clé étrangère qui identifie la classe d'appartenance 

III-A-2. Requête R_ClassesAnimales

Elle affiche les classes animales et leur appartenance par une relation réflexive sur les champs IdClasse et IDClasseAppartenance de la même table.

R_ClassesAnimales
Sélectionnez
SELECT T_ClasseAnimale.IdClasse, T_ClasseAnimale.NomClasse, T_ClasseAnimale_1.NomClasse AS ClasseMere
FROM T_ClasseAnimale LEFT JOIN T_ClasseAnimale AS T_ClasseAnimale_1 ON T_ClasseAnimale.IDClasseAppartenance = T_ClasseAnimale_1.IdClasse
ORDER BY T_ClasseAnimale.IdClasse;

III-A-3. Affichage des données

Liste des classes et leur classe mère

R_ClassesAnimales

IdClasse

NomClasse

ClasseMere

1

Le monde animal

 

2

Les invertébrés

Le monde animal

3

Les vertébrés

Le monde animal

4

Les insectes

Les invertébrés

5

Les mollusques

Les invertébrés

6

Les mammifères

Les vertébrés

7

Les oiseaux

Les vertébrés

8

Les poissons

Les vertébrés

9

Les amphibiens

Les vertébrés

10

Les reptiles

Les vertébrés

III-A-4. Génération de l'arbre avec du code VBA

L'objectif est d'afficher par du code et dans un contrôle CtrlTree un arbre général à partir des données contenues dans la requête R_ClassesAnimales.

Le contrôle CtrlTree nécessite l'installation d'une bibliothèque. Pour plus de détails, vous pouvez consulter la page Librairie pour arbres, grilles et listes sous AccessLibrairie pour arbres, grilles et listes sous Access.

Fonction récursive appelée dans une fonction principale permet de générer l'arbre à partir des données.

fonction récursive
Sélectionnez
'******************************************************************************************************************
'**********************Fonction appelante pour générer l'arbre des classes à partir des données********************
'******************************************************************************************************************
Public Function ClassificationAnim(rs As DAO.Recordset, idClasseAppartenance As Long, t As CtrlTree)
'rs : recordset relié à la requête R_ClassesAnimales pour afficher les classes et leur appartenance
'idClasseAppartenance : Identifiant de la classe principale
't : Contrôle CtrlTree dans lequel on va afficher la classification
Dim debut As Variant
Dim idClasse As Long
Dim classe As String

debut = rs.Bookmark ' sauvegarde du pointeur de début
   
idClasse = rs!idClasse ' enregistrement de l'identifiant de la classe pour savoir si elle a des sous-classes.
   
classe = rs!NomClasse
   
   If IsNull(rs!idClasseAppartenance) Then ' si racine pas de parent
      t.ElementAdd classe, CStr(idClasse) ' ajout de la classe à la racine de l'arbre
   Else ' sinon mentionner le parent
      t.ElementAdd classe, CStr(idClasse), CStr(idClasseAppartenance) ' ajout de la classe animale sur un noeud
   End If

rs.FindFirst "(IDClasseAppartenance=" + CStr(idClasse) + ")" ' recherche de la 1re sous-classe animale
      
      Do While Not rs.NoMatch ' parcours des sous-classes
        ClassificationAnim rs, CStr(idClasse), t ' appel récursif de la fonction pour IdClasse
        rs.FindNext "(IDClasseAppartenance=" + CStr(idClasse) + ")" ' recherche de la prochaine sous-classe animale
      Loop

rs.Bookmark = debut ' retour au début

End Function

Fonction principale permettant d'appeler la sous-routine :

fonction appelante
Sélectionnez
'******************************************************************************************************************
'*********************Fonction appelante pour générer l'arbre des classes à partir des données*********************
'******************************************************************************************************************
Public Function GenererClassificationAnim(t As CtrlTree)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("T_ClasseAnimale", dbOpenSnapshot) ' ouverture de la requête

rs.FindFirst ("IsNull([IDClasseAppartenance])") ' position à la racine, classe animale sans ascendants

Call ClassificationAnim(rs, rs!idClasse, t) ' appel de la fonction récursive

' Libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Function

Cette fonction principale peut se généraliser pour prendre en compte 1 ou plusieurs racines :

fonction appelante
Sélectionnez
'******************************************************************************************************************
'*********************Fonction appelante pour générer l'arbre des classes à partir des données*********************
'******************************************************************************************************************
Public Function GenererClassificationAnim2(t As CtrlTree)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("T_ClasseAnimale", dbOpenSnapshot) ' ouverture de la requête

rs.FindFirst ("IsNull([IDClasseAppartenance])") ' position à la racine, classe animale sans ascendants

Do Until rs.NoMatch ' Boucle sur les racines de l'arbre
   Call ClassificationAnim(rs, rs!idClasse, t) ' appel de la fonction récursive
   rs.FindNext ("IsNull([IDClasseAppartenance])") ' recherche prochaine racine
Loop

' Libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Function

III-A-5. Création du formulaire F_ClassificationAnim

Le formulaire F_ClassificationAnim en mode création, il comprend le sous-formulaire SF_Arbre contenant le contrôle image pour dessiner l'arbre.

Image non disponible
Création du formulaire F_ClassificationAnim

III-A-5-a. Code sur chargement du formulaire

Code sur chargement du formulaire
Sélectionnez
Private Sub Form_Load()

Set oTree = CreateTGLControl(CtrlTree, Me.SF_Arbre) ' Création du contrôle oTree relié au sous-formulaire

oTree.Clear

oTree.FontSize = 18 ' Définition de la taille des caractères du contrôle
oTree.FontColor = Me.Titre.ForeColor ' Définition de la couleur des caractères

Call GenererClassificationAnim(oTree) ' Génération de l'arbre des classes animales dans le contrôle

oTree.ExpandAll ' déploiement de l'arbre
oTree.Refresh 'Rafraîchissement du contrôle pour afficher son contenu dans le sous-formulaire

End Sub

III-A-6. Aperçu de l'arbre

Image non disponible
classification du règne animal

III-B. Arbre généalogique

Image non disponible
arbre généalogique

Pour créer ce type d'arbre, on a besoin d'enregistrer dans une table d'individus T_Personne les liens entre ces différentes personnes.

III-B-1. Table T_Personne

T_Personne

Nom du champ 

Type du champ 

Description 

NumPersonne 

Entier long 

Numéro de la personne 

NomPersonne 

Texte 

Nom de la personne 

PrenomPersonne 

Texte 

Prénom de la personne 

Sexe 

Texte 

Homme/Femme 

IDConjoint 

Entier long 

Clé étrangère liée à l'identifiant de la personne 

IDPere 

Entier long 

Clé étrangère liée à l'identifiant de la personne 

IDMere 

Entier long 

Clé étrangère liée à l'identifiant de la personne 

III-B-2. Requête R_Personnes

Elle affiche les personnes et leur conjoint par une relation réflexive sur les champs NumPersonne et Conjoint.

R_Personnes
Sélectionnez
SELECT T_Personne.NumPersonne, T_Personne.NomPersonne, T_Personne.PrenomPersonne, T_Personne.Sexe, T_Personne.Conjoint, T_Personne_1.NomPersonne AS NomConjoint, T_Personne_1.PrenomPersonne AS PrenomConjoint, T_Personne.Pere, T_Personne.Mere, (IsNull([T_Personne].[Pere]) And IsNull([T_Personne].[Mere])) And (IsNull([T_Personne_1].[Pere]) And IsNull([T_Personne_1].[Mere])) AS Racine
FROM T_Personne LEFT JOIN T_Personne AS T_Personne_1 ON T_Personne.Conjoint = T_Personne_1.NumPersonne;

III-B-3. Affichage des données

Liste des personnes et leur conjoint

R_Personnes

NumPersonne

NomPersonne

PrenomPersonne

Sexe

NomConjoint

PrenomConjoint

Pere

Mere

1

Dupond

Martin

Homme

Carpentier

Maryse

   

2

Carpentier

Maryse

Femme

Dupond

Martin

   

3

Dupond

Luc

Homme

Martin

Renée

Dupond Martin

Carpentier Maryse

4

Dupond

Marie

Femme

   

Dupond Martin

Carpentier Maryse

5

Martin

Renée

Femme

Dupond

Luc

   

6

Dupond

Jean

Homme

   

Dupond Luc

Martin Renée

7

Dupond

Ludovic

Homme

   

Dupond Luc

Martin Renée

III-B-4. Génération de l'arbre avec du code VBA

L'objectif est d'afficher par du code et dans un contrôle CtrlTree un arbre généalogique à partir des données contenues dans la requête R_Personnes.

Le contrôle CtrlTree nécessite l'installation d'une bibliothèque. Pour plus de détails, vous pouvez consulter la page Librairie pour arbres, grilles et listes sous AccessLibrairie pour arbres, grilles et listes sous Access.

Une fonction récursive appelée dans une fonction principale permet de générer l'arbre à partir des données.

fonction récursive
Sélectionnez
'*********************************************************************************************************************
'*********************Fonction récursive pour générer l'arbre généalogique à partir des données***********************
'*********************************************************************************************************************
Public Function ArbreGen(rs As DAO.Recordset, idParent As Long, t As CtrlTree)
'rs : recordset relié à la requête R_Personnes pour afficher les personnes et leurs liens
'idParent : Identifiant du parent
't : Contrôle treeview dans lequel on va afficher l'arbre généalogique
Dim debut As Variant
Dim NumPersonne As Long
Dim personne As String, conjoint As String

debut = rs.Bookmark ' sauvegarde du pointeur de début
   
NumPersonne = rs!NumPersonne ' enregistrement du numéro de la personne pour savoir si elle a des descendants
   
personne = rs!NomPersonne + " " + rs!PrenomPersonne

   If Not IsNull(rs!conjoint) Then
      conjoint = rs!NomConjoint + " " + rs!PrenomConjoint
   End If
   
   If rs!Racine Then ' si racine pas de parent
      t.ElementAdd personne + " - " + conjoint, rs!NumPersonne ' ajout de la personne et son conjoint à la racine de l'arbre
   Else ' sinon mentionner le parent
      t.ElementAdd personne + " - " + conjoint, rs!NumPersonne, CStr(idParent) ' ajout de la personne et son conjoint sur un noeud de l'arbre
   End If

rs.FindFirst "(IDPere=" + CStr(NumPersonne) + ") or " + "(IDMere=" + CStr(NumPersonne) + ")" ' recherche du 1er enfant de cette personne
      
      Do While Not rs.NoMatch ' parcours des enfants
        ArbreGen rs, CStr(NumPersonne), t ' appel récursif de la fonction pour NumPersonne
        rs.FindNext "(IDPere=" + CStr(NumPersonne) + ") or " + "(IDMere=" + CStr(NumPersonne) + ")" ' recherche du prochain enfant
      Loop

rs.Bookmark = debut ' retour au début

End Function

Une fonction principale permettant d'appeler la sous-routine :

fonction appelante
Sélectionnez
'*******************************************************************************************************************
'*********************Fonction appelante pour générer l'arbre généalogique à partir des données*********************
'*******************************************************************************************************************
Public Function GenererArbreGen(t As CtrlTree)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("R_Personnes", dbOpenSnapshot) ' Ouverture de la requête

rs.FindFirst ("Racine=true") ' Position à la racine, personnes sans parent mentionné

Call ArbreGen(rs, rs!NumPersonne, t) ' appel de la fonction récursive

' Libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Function

III-B-5. Création du formulaire F_ArbreGen

Le formulaire F_ArbreGen en mode création, il comprend le sous-formulaire SF_Arbre contenant le contrôle image pour dessiner l'arbre.

Image non disponible
Création du formulaire F_ArbreGen

III-B-5-a. Code sur chargement du formulaire

Code sur chargement du formulaire
Sélectionnez
Private Sub Form_Load()

Set oTree = CreateTGLControl(CtrlTree, Me.SF_Arbre) ' Création du contrôle oTree relié au sous-formulaire

oTree.Clear

oTree.FontSize = 18 ' Définition de la taille des caractères du contrôle
oTree.FontColor = Me.Titre.ForeColor ' Définition de la couleur des caractères

Call GenererClassificationAnim(oTree)  ' Génération de l'arbre généalogique dans le contrôle

oTree.ExpandAll ' déploiement de l'arbre
oTree.Refresh 'Rafraîchissement du contrôle pour afficher son contenu dans le sous-formulaire

End Sub

III-B-6. Aperçu de l'arbre

Image non disponible
arbre généalogique

IV. La base de données à télécharger

La base jointeBD Classification présente les différents exemples décrits dans le tutoriel, elle est au format Access 2000.

V. Remerciements

Je tiens à remercier argyronet et Jean-Philippe André pour m'avoir conseillé pour la réalisation de cet article, ainsi que Claude Leloup pour sa relecture.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2017 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.