Extraction des données de doublons avec Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraire les données de doublons avec Access

L'objectif de cette formation est de démontrer la souplesse et la puissance d'Access lorsqu'il s'agit de manipuler des données de bases de données. En l'occurrence ici, nous souhaitons réaliser un parallèle avec la formation Excel sur l'extraction des données de doublons. D'un tableau de la liste des communes françaises sur 39200 lignes, nous avions construit une formule complexe, sur la base des fonctions Excel Index et Equiv, afin d'extraire les données correspondantes des villes portant exactement le même nom.



Nous proposons d'importer ces données dans une nouvelle base Access. Nous allons donc réaliser la procédure inverse à celle enseignée par la formation pour exporter les données Access vers Excel. Nous construirons un formulaire sur la base de ces données importées. Un moteur de recherche devra permettre de saisir le nom de la commune redondante, pour lequel nous souhaitons extraire toutes les informations liées. Nous verrons qu'aucune formule complexe ne sera nécessaire. Une simple requête avec une clause WHERE dynamique fera l'affaire.

Importation des données Excel dans Access
Les données à importer doivent d'abord être récupérées.

• Télécharger le classeur Excel extraction-donnees-doublons.xlsx en cliquant sur son lien,
• Démarrer Access et créer une base de données vierge,
• La nommer : extraction_doublons.accdb,
• Définir le même emplacement que le dossier où a été téléchargé le classeur Excel,
• Puis, cliquer sur le bouton Créer,

Une nouvelle table est proposée en mode feuille de données. En effet, l'ossature d'une base de données se construit sur les tables et leurs relations. Mais comme nous souhaitons importer ces données, nous créerons la table de réception en même temps.

• Cliquer sur la croix de son onglet pour fermer cette table,
• Cliquer sur l'onglet Données externes en haut de la fenêtre Access pour activer son ruban,
• Dans la section Importer et lier du ruban, cliquer sur le bouton Excel,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Parcourir,
• Double cliquer sur le fichier extraction-donnees-doublons.xlsx dans son dossier,
• Conserver l'option cochée par défaut : Importer les données sources dans une nouvelle table,
• Puis, cliquer sur Ok pour déclencher l'assistant importation de feuille de calcul,

Dans la première étape de l'assistant, deux options sont proposées : Afficher les feuilles de données et Afficher les plages nommées. Nous n'avons nommé aucune plage de cellules dans le classeur source. Nous devons donc compter sur l'assistant pour détecter les bornes du tableau à importer dans une nouvelle table Access. Vous constatez à ce titre que la boîte de dialogue propose un aperçu où les éléments apparaissent bien dans leurs colonnes respectives.

• Conserver l'option : Afficher les feuilles de données,
• Puis, cliquer sur le bouton Suivant,

Si un message d'erreur sur les noms de champs intervient, vous devez cliquer sur Ok pour l'ignorer. Access peut être gêné par la présence d'espaces ou de caractères latins dans ces titres de colonnes. Nous renommerons ces champs explicitement une fois les données importées dans la nouvelle table créée, avec l'assistant.

• Cocher la case : Première ligne contient les en-têtes de colonnes,

Il s'agit en effet des titres des colonnes du tableau source, issu du classeur Excel. En les désignant ainsi, Access les transforme en champs de table. Ces titres ne doivent pas être considérés comme des données en effet.

• Cliquer sur le bouton Suivant pour poursuivre le processus d'importation,

Cette étape est fondamentale car elle permet de définir la structure de la table de réception. En outre, nous allons typer les champs au plus juste et si nécessaire, nous allons renommer certains d'entre eux. La première colonne (Champ1) étant sélectionnée par défaut :

• Dans la zone Nom du champ, taper Nom_commune,
• Dans la zone champ indexé, choisir Oui - Avec doublons,

Pour cette première colonne qui recense toutes les villes françaises, nous attribuons un nom sans espace ni caractères latins. Nous choisissons une indexation du champ avec doublons. Un champ indexé permet d'améliorer les temps de réponses. Bien sûr il faut indexer seulement les champs pour lesquels cette action est justifiée, au risque d'obtenir l'effet inverse. Le champ en question ne doit pas contenir une majorité de valeurs redondantes et doit être utilisé régulièrement pour des recherches. Et c'est bien le cas ici.

• Dans l'aperçu de la boîte de dialogue, cliquer sur l'en-tête du champ2 pour l'activer,
• Le nommer Code_insee et choisir de ne pas l'indexer,
• Dans la zone Type de données, choisir Texte court,

Un code_insee est une référence unique pour désigner chaque commune de façon explicitement indépendante, malgré des noms parfois identiques. Nous pourrions l'utiliser comme une clé primaire qui est forcément un champ indexé sans doublons.

• Cliquer sur l'en-tête du champ3 pour l'activer,
• Le nommer Code_postal et ne pas l'indexer,
• Lui attribuer un type de données Texte court,

Bien que les données de ces deux derniers champs ressemblent à des données numériques, nous leur attribuons le type de données Texte court. Tout d'abord ces deux champs ne sont pas vraiment considérés comme numériques dans la mesure où nous ne ferons aucune opération, ni sur les codes postaux, ni sur les codes Insee. De plus un type texte court est plus permissif que le type entier pour l'importation de données. Il suffirait que des caractères mal interprétés soient saisis dans l'un ou l'autre champ, pour que l'importation échoue.

• Cliquer maintenant sur le dernier champ : Coordonnées Gps,
• Le renommer : Coordonnees_gps,

Ainsi nous remplaçons le caractère latin et l'espace. Le type de données Texte court convient très bien. Bien entendu, ce champ ne doit pas être indexé.

• Cliquer sur le bouton Suivant de l'assistant importation,
• Conserver l'option Laisser Access ajouter une clé primaire,
• Puis, cliquer sur le bouton Suivant,

Toute table sauf exception doit être créée avec une clé primaire dans un gestionnaire de base de données comme Access. Une clé primaire permet d'identifier chaque enregistrement de façon unique. D'ailleurs, le champ qui la porte est par défaut indexé sans doublons. Nous préférons laisser Access ajouter un champ unique, auto-incrémenté car nous ne sommes pas certains de l'intégrité des données sources.

• Dans la dernière étape, la nommer Liste_communes et valider,

La table ainsi créée s'affiche avec tous les enregistrements importés en respectant le typage et la structure définis pendant le processus d'importation.

Extraire les doublons d'une base de données
Même si l'objectif ici, a l'instar de la formation Excel, est d'extraire toutes les données des communes redondantes, Access propose des requêtes toutes faites. Parmi elles, la requête Trouver les doublons permet d'afficher, selon le champ désigné, tous les enregistrements pour lesquels les homonymes ont été trouvés.

• Fermer la table Liste_communes en cliquant sur la croix de son onglet,
• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Requêtes, cliquer sur le bouton Assistant requête,
• Dans la boîte de dialogue qui suit, sélectionner Assistant requête Trouver les doublons,
• Puis, cliquer sur le bouton Ok,
• Dans l'étape suivante, la table étant présélectionnée, cliquer sur le bouton Suivant,
• Basculer ensuite le champ Nom_commune dans la liste de droite avec le bouton Flèche,

De nombreuses villes portent effectivement le même nom, avec la même orthographe en France, mais ne sont pas situées dans le même département. Par curiosité, nous souhaitons regrouper tous les noms de communes qui ont au moins un homonyme.

• Cliquer sur le bouton Suivant,
• Basculer tous les champs restants dans la liste de droite avec le bouton double Flèche,

Ainsi, nous indiquons que pour tous les doublons trouvés sur le nom de la commune, nous souhaitons afficher les renseignements issus de tous les champs.

• Cliquer sur le bouton Suivant,
• Nommer la requête : Doublons_communes et cliquer sur le bouton Terminer,

La requête affiche alors la sélection en mode feuille de données. 9016 enregistrements sont concernés, soit un volume conséquent. Comme l'illustre la capture ci-dessus, la commune d'Aigremont est répétée quatre fois. Pourtant il ne s'agit jamais de la même, en attestent les codes Insee différents, prouvant qu'il s'agit de communes du même nom, situées dans des départements différents.

Formulaire de recherche pour extraire les données
Nous souhaitons donc récupérer les informations d'une commune par saisie de son nom, dans une zone de saisie, comme un moteur de recherche. Un sous formulaire doit instantanément lister toutes les données de la commune, autant de fois qu'elle est répétée. Il s'agit d'extraire les données de doublons. Avec Excel, nous avions dû bâtir une formule complexe difficile à mettre en oeuvre. Avec Access, moyennant la construction du formulaire, nous verrons que ces extractions sont triviales. Ce formulaire étant spécifique, il doit être bâti de toutes pièces.

• Fermer la requête en cliquant sur la croix de son onglet,
• Dans le ruban Créer, cliquer sur le bouton Création de formulaire,

Un formulaire vierge s'affiche au centre de l'écran, en mode création.

• Enregistrer ce formulaire (CTRL + S) sous le nom : Extraction_doublons,
• A l'aide de la zone Contrôles du ruban Création, ajouter une zone de texte (ab),
• Cliquer sur le bouton Feuille de propriétés du ruban Création si cette dernière n'est pas visible,
• Activer l'onglet Autres de la feuille de propriétés de la zone de texte,
• Dans sa propriété Nom, saisir :moteur,
• Activer l'onglet Format de sa feuille de propriétés,
• Dans sa propriété Format, taper le symbole supérieur (>),

La formation Access sur les masques de saisie et le formatage des champs, nous avait appris à utiliser ce symbole pour formater automatiquement, toutes les saisies en majuscules.

• Dans la section Contrôles du ruban Création, cliquer sur le symbole du bouton (xxx),
• Le tracer à droite de la zone de texte moteur,
• Cliquer sur le bouton Annuler de l'assistant qui surgit,
• Activer l'onglet Autres de sa feuille de propriétés,
• Dans la propriété Nom, saisir extraire,
• Activer l'onglet Format de sa feuille de propriétés,
• Dans sa propriété Légende, saisir le texte : Rechercher et extraire,
• Dans la section Contrôles du ruban Création, choisir le Sous formulaire/Sous état,
• Le tracer sous la zone de saisie et le bouton,
• Activer l'onglet Autres de sa feuille de propriétés,
• Dans sa propriétés Nom, saisir :resultats,
• Enregistrer les modifications (CTRL + S),

Cette extraction de données doit se faire sans le concours du code VBA Access. Le critère est pourtant dynamique puisqu'il dépend de la saisie utilisateur dans le moteur de recherche. Comme nous l'a enseignée la formation Access pour extraire et filtrer les données, l'astuce consiste à construire une requête avec une clause Where dynamique sur le champ Nom_commune. Ce critère doit attendre la valeur saisie dans la zone moteur pour effectuer la sélection. Il suffit enfin de définir cette requête comme source de données du sous formulaire. En fonction de l'information saisie dans le moteur de recherche, la requête dynamique réalise une sélection correspondante qui est restituée dans le sous formulaire.

• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Cliquer ensuite sur le bouton Création de requête,
• Dans la boîte de dialogue qui surgit, double cliquer sur la table Liste_communes,
• Puis, cliquer sur le bouton Fermer,
• Glisser tous les champs de la table sur la grille de requête,

A ce stade, nous avons construit une requête sélection qui affichera une vue identique à celle de la table, puisqu'aucune restriction par le biais d'un critère n'a encore été posée.



Il s'agit d'indiquer que cette requête doit extraire uniquement les enregistrements pour lesquels le champ Nom_commune, est strictement identique à l'information saisie par l'utilisateur, dans le moteur de recherche du formulaire. Dans la zone Critères pour le champ Nom_commune, nous devons donc réaliser la correspondance entre ces champs, à l'aide du Générateur d'expression par exemple.

• Cliquer avec le bouton droit de la souris dans la zone de critères du champ Nom_commune,
• Dans le menu contextuel, choisir Créer,
• Dans l'arborescence de la liste de gauche du générateur, déployer l'affichage des formulaires,
• Puis, sélectionner le formulaire Extraction_doublons,
• Double cliquer alors sur le champ moteur dans la section centrale,

Le générateur d'expression produit alors la syntaxe de correspondance pour le critère dynamique de la requête.

• Valider cette expression en cliquant sur le bouton Ok,
• Enregistrer la requête (CTRL + S) sous le nom : Extraction_dynamique,

Bien sûr, si nous exécutons la requête, le résultat produit à ce stade, est vide. Comme aucune information n'est encore saisie dans le moteur de recherche, le critère de la requête ne trouve aucune correspondance.

Source dynamique d'un formulaire
La requête dynamique que nous venons de construire, ne doit pas s'exécuter indépendamment. Elle doit livrer ses résultats d'extraction dans le sous-formulaire du formulaire que nous sommes en train de concevoir.

• Fermer la requête en cliquant sur la croix de son onglet,
• Afficher le formulaire Extraction_doublons en mode création,
• Sélectionner le sous formulaire resultats,
• Activer l'onglet Données de sa feuille de propriétés,
• Dans sa propriété Objet source, désigner la requête Extraction_dynamique,

La source de données du sous formulaire est ainsi définie à l'instant t. Mais aucun événement ne permet encore de réactualiser cette source si le critère dynamique de la requête venait à changer, par le biais d'une saisie utilisateur. Cela signifie qu'à ce stade, le sous formulaire restera inexorablement muet.

Pour pallier ce défaut, nous allons associer une macro automatique au bouton extraire, de manière à rafraîchir les données et donc à forcer le recalcul.

• Sélectionner le bouton Extraire sur le formulaire,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le bouton de son événement Au clic,
• Dans la liste, choisir Générateur de macro,
• Dans l'éditeur de macro qui apparaît, choisir l'action AfficherTousEnreg,
• Enregistrer les modifications et fermer l'éditeur de macro,

Nous aurions pu être tentés de choisir la commande Actualiser ou ActualiserEnregistrements. Mais ces dernières auraient conduit au message d'erreur suivant : la commande actualiser n'est pas disponible pour l'instant. La commande AfficherTousEnreg contourne le souci dans la mesure où elle rafraîchit les enregistrements et force le recalcul de la source de données du sous-formulaire.

• Enfoncer la touche F5 du clavier pour exécuter le formulaire,

A l'ouverture, puisque la zone de recherche est vide, le sous formulaire ne présente aucune donnée. C'est le résultat de la requête dont le critère dynamique est vide.

• Taper le nom de commune Valence par exemple,
• Puis cliquer sur le bouton Rechercher et extraire,

Instantanément, les informations du sous formulaire se mettent à jour grâce au rafraichissement des données, commandé par l'exécution de la macro AfficherTousEnreg, au clic sur le bouton. La requête récupère le critère Valence sur le nom de la commune et livre ainsi les résultats de son extraction. Et bien qu'il y ait des doublons, toutes les informations correspondantes sont rapatriées et listées.

• Taper le nom de commune Aigremont à la place de Valence puis cliquer sur le bouton,

L'extraction dynamique fonctionne parfaitement. Contrairement à Excel où des formules complexes avaient été nécessaires, ici ni code VBA, ni calculs n'ont été utilisés pour produire le même résultat, mais avec encore plus de souplesse et d'efficacité.