Classification des données et structures arborescentes avec Access

Denis Hulo

Objectif : apprendre à réaliser une classification des données et à générer des structures arborescentes avec Access.

Niveau requis : avancé.

Commentez cet article : Commentez

- Titre : Classification des données et structures arborescentes avec Access

- Auteur : Denis Hulo

- Parution : 4 juillet 2017

- Mise à jour : 3 octobre 2019

- Public visé :

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

Sommaire

- I. Introduction

- II. Classification simple de données

- II-A. Classement d'articles

- II-A-1. Tables nécessaires

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

- II-A-3. Affichage de résultats

- III. Structures arborescentes

- III-A. Classification du règne animal

- III-A-1. Table T_ClasseAnimale

- III-A-2. Requête R_ClassesAnimales

- III-A-3. Affichage des données

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

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

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

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

- III-B. Arbre généalogique

- III-B-1. Table T_Personne

- III-B-2. Requête R_Personnes

- III-B-3. Affichage des données

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

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

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

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

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

- V. Remerciements

I - Introduction

Nous présenterons dans cet article des exemples de classification de données et de génération de structures arborescentes réalisés avec Access :

- classification d'articles par groupes, familles et sous-familles ;

- classification du règne animal ;

- génération d'un arbre généalogique.

Après avoir définit le concept de classification des données et celui de structure arborescente, on décrira pour chaque exemple la structure des objets et du code VBA permettant d'enregistrer et d'afficher les données.

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

1

Groupes

A, B, C..

2

Familles

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

3

Sous-familles

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

4

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

A

A001

A001.001

A001.001.001

A

A001

A001.001

A001.001.002

...

...

...

...

A

A001

A001.002

A001.002.001

A

A001

A001.002

A001.002.002

...

...

...

...

A

A002

A002.001

A002.001.001

A

A002

A002.001

A002.001.002

...

...

...

...

A

A002

A002.002

A002.002.001

A

A002

A002.002

A002.002.002

...

...

...

...

B

B001

B001.001

B001.001.001

B

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 entre elles les tables T_GroupeArticle, T_FamilleArticle et T_SousFamilleArticle, 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 tables T_Article, T_Groupe, T_Famille et T_SousFamille.

R_ClassificationArticles

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

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

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

'*********************************************************************************************************************
'**********************Fonction récursive 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 ' variable pour enregistrer le signet de début
Dim idClasse As Long ' variable contenant l'identifiant de la classe
Dim classe As String ' variable contenant le nom de la classe

debut = rs.Bookmark ' sauvegarde du pointeur de début

rs.FindFirst "(IDClasseAppartenance=" + CStr(idClasseAppartenance) + ")" ' recherche de la 1re sous-classe animale

Do While Not rs.NoMatch ' parcours des sous-classes
idClasse = rs!idClasse ' copie de l'identifiant de la classe dans la variable
classe = rs!Nomclasse ' copie du nom de la classe dans la variable

t.ElementAdd classe, CStr(idClasse), CStr(idClasseAppartenance) ' ajout de la classe animale sur un noeud de l'arbre

ClassificationAnim rs, idClasse, t  ' appel récursif de la fonction pour IdClasse

rs.FindNext "(IDClasseAppartenance=" + CStr(idClasseAppartenance) + ")"  ' 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

'*********************************************************************************************************************
'**********************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

'*********************************************************************************************************************
'********************Fonction appelante v2 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 à une racine, classe animale sans ascendants

Do Until rs.NoMatch ' Boucle sur les racines de l'arbre
t.ElementAdd rs!Nomclasse, CStr(rs!idClasse) ' ajout de la classe à une racine de l'arbre
Call ClassificationAnim(rs, rs!idClasse, t) ' appel de la fonction récursive
rs.FindNext ("IsNull([IDClasseAppartenance])") ' recherche de la 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.

Création du formulaire F_ClassificationAnim

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

Code sur chargement du formulaire

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

classification du règne animal

III-B - Arbre généalogique

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

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 parents par des relations reflexives sur les champs NumPersonne, IdPere et IdMere.

R_Personnes

SELECT T_Personne.NumPersonne AS IDPersonne, T_Personne.NomPersonne, T_Personne.PrenomPersonne, T_Personne.Sexe, T_Personne.IDPere, [T_Personne_1].[NomPersonne] & " " & [T_Personne_1].[PrenomPersonne] AS Pere, T_Personne.IDMere, [T_Personne_2].[NomPersonne] & " " & [T_Personne_2].[PrenomPersonne] AS Mere, (IsNull([T_Personne].[IDPere]) And IsNull([T_Personne].[IDMere])) AS Racine
FROM (T_Personne LEFT JOIN T_Personne AS T_Personne_1 ON T_Personne.IDPere = T_Personne_1.NumPersonne) LEFT JOIN T_Personne AS T_Personne_2 ON T_Personne.IDMere = T_Personne_2.NumPersonne;

III-B-3 - Affichage des données

Liste des personnes et leurs parents

R_Personnes

NumPersonne

NomPersonne

PrenomPersonne

Sexe

IdPere

Pere

IdMere

Mere

1

Dupond

Martin

Homme

2

Carpentier

Maryse

Femme

3

Dupond

Luc

Homme

1

Dupond Martin

2

Carpentier Maryse

4

Dupond

Marie

Femme

1

Dupond Martin

2

Carpentier Maryse

5

Martin

Renée

Femme

6

Dupond

Jean

Homme

3

Dupond Luc

5

Martin Renée

7

Dupond

Ludovic

Homme

3

Dupond Luc

5

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

'*********************************************************************************************************************
'*********************Fonction récursive pour générer l'arbre généalogique à partir des données***********************
'*********************************************************************************************************************
Public Function ArbreGen(rs As DAO.Recordset, idPere As Long, idMere As Long, t As CtrlTree)
'rs : recordset relié à la requête R_Personnes pour afficher les personnes et leurs liens
'idPere : identifiant du père
'idMere : identifiant de la mère
't : contrôle treeview dans lequel on va afficher l'arbre généalogique
Dim debut As Variant ' variable pour enregistrer le signet de début
Dim idPersonne As Long, idConjoint As Long ' identifiants du couple
Dim couple As String ' variable contenant les noms et prénoms des parents

debut = rs.Bookmark ' copie du pointeur de début

rs.FindFirst "(IDPere=" + CStr(idPere) + ") and " + "(IDMere=" + CStr(idMere) + ")" ' recherche du 1er enfant des parents

Do While Not rs.NoMatch ' parcours des enfants

' on identifie la personne et son conjoint
idPersonne = rs!idPersonne: idConjoint = 0
couple = rs!nomPersonne & " " & rs!prenomPersonne

IdentCouple rs, idPersonne, idConjoint, couple, t ' on identifie le couple avec l'identifiant de la personne

' ajout de la personne et de son conjoint sur un noeud de l'arbre
t.ElementAdd couple, CStr(idPersonne) & "-" & CStr(idConjoint), CStr(idPere) & "-" & CStr(idMere)
Call ArbreGen(rs, idPersonne, idConjoint, t)   ' appel de la fonction récursive

rs.FindNext "(IDPere=" + CStr(idPere) + ") and " + "(IDMere=" + CStr(idMere) + ")" ' 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

'*********************************************************************************************************************
'*********************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 ' référence à la base courante
Dim rs As DAO.Recordset ' variable recordset
Dim idPersonne As Long, idConjoint As Long ' identifiants du couple
Dim couple As String ' variable contenant les noms et prénoms des parents

Set db = CurrentDb ' référence à la base courante
Set rs = db.OpenRecordset("R_Personnes", dbOpenSnapshot) ' ouverture du recordset basé sur la requête R_Personnes

rs.FindFirst ("Racine=true") ' position à la racine, personnes sans parents mentionnés

Do While Not rs.NoMatch ' parcours des racines

idPersonne = rs!idPersonne: idConjoint = 0 ' initialisation des identifiants du couple
couple = rs!nomPersonne & " " & rs!prenomPersonne

IdentCouple rs, idPersonne, idConjoint, couple, t ' on identifie le couple avec l'identifiant de la personne

If (idPersonne <> 0) And (idConjoint <> 0) Then
t.ElementAdd couple, CStr(idPersonne) & "-" & CStr(idConjoint)  ' ajout de la personne et son conjoint à la racine de l'arbre
Call ArbreGen(rs, idPersonne, idConjoint, t)   ' appel de la fonction récursive
End If

rs.FindNext ("Racine=true") ' recherche prochaine racine

Loop

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

Création du formulaire F_ArbreGen

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

Code sur chargement du formulaire

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

arbre généalogique

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

La base jointe 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.