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

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.. 

On obtiendra donc les correspondances suivantes entre les différentes références :

Tableau de correspondance entre les références
Réf. Groupe  Réf. Famille  Réf. Sous-famille  Réf. Article 
A001  A001.001  A001.001.001 
A001  A001.001  A001.001.002 
...  ...  ...  ... 
A001  A001.002  A001.002.001 
A001  A001.002  A001.002.002 
...  ...  ...  ... 
A002  A002.001  A002.001.001 
A002  A002.001  A002.001.002 
...  ...  ...  ... 
A002  A002.002  A002.002.001 
A002  A002.002  A002.002.002 
...  ...  ...  ... 
B001  B001.001  B001.001.001 
B001  B001.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.

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

fonction récursive ClassificationAnim
Cacher/Afficher le codeSélectionnez

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

    t.ElementAdd rs!Nomclasse, CStr(rs!idClasse) ' ajout de la classe à la racine de l'arbre

    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
Cacher/Afficher le codeSélectionnez

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 ArbreGen
Cacher/Afficher le codeSélectionnez

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 parents mentionnés

    t.ElementAdd rs!NomPersonne + " " + rs!PrenomPersonne + " - " + Nz(rs!NomConjoint, "") + " " + Nz(rs!PrenomConjoint, ""), _
                                    rs!NumPersonne ' ajout de la personne et son conjoint à la racine de l'arbre
    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 ni 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.