Nous allons essayer, à travers ces écrits, de comprendre les mécanismes et les fonctionnalités fondamentales d'Access. L'objectif de cette partie n'est pas de maîtriser ce produit, mais d'en avoir une bonne vue d'ensemble.
Dans le cursus que nous vous proposons, vous vous situez ici:
|
Access: Les Bases
Après un rapide tour d'horizon d'une méthodologie d'analyse simplifiée, nous abordons la questions des tables, des requêtes sélection, des formulaires et enfin, des états. Les cours suivants permettent un perfectionnement sur les requêtes et les états (Exploitation des données), sur les formulaires, accompagnés de l'apprentissage des macros (Interface et automatisation) ou encore, de la mise en place de la sécurité (Sécurité) Il sera possible de finir par un cours sur le VBA, découpé en trois parties: 1- les bases du langage (Visual Basic pour Application), 2- perfectionnement de ces bases (Perf VBA) 3- développement Client-Serveur avec Access comme frontal (VBA C/S) |
Pourquoi cette progression ?
Prenons un exemple de la vie courante : Vous avez acquis un terrain, et vous souhaitez construire. Par quoi allez-vous commencer ?
La plupart répondront : "Les fondations".
Cela n'est pas judicieux. En effet, il est préférable de commencer par faire un plan. Ensuite, nous basant sur ce plan, il sera facile de faire le gros-œuvre, puis de mettre en place les éléments fonctionnels, tels la plomberie et l'électricité, pour finir par la décoration (peinture, parquet et autres papiers peints).
Le plan, c'est le résultat de l'analyse.
Le gros-œuvre, correspond aux tables et relations de la base de données ; la partie stockage des données à gérer.
La plomberie, l'électricité, etc., tous ces éléments fonctionnels représentent les requêtes, qui vous permettront d'exploiter les données brutes présentes dans les tables.
Enfin, les formulaires et les états sont les outils vous permettant de créer l'aspect cosmétique, la décoration, de votre application, aussi bien à l'écran (formulaires) qu'à l'impression (états).
J'aimerais remercier tout particulièrement mon père et mon épouse, qui furent mes premiers relecteurs, censeurs et supporters, ainsi que site d'entraide des développeurs pour le soutien qu'il m'a apporté dans le cadre de la correction de ces écrits, et tout particulièrement à Thomas Lebrun et Stéphane Eyskens pour l'énorme travail que cela leur a demandé.
Je vous propose de retrouver maintenant l'ensemble de la formation que je fournis habituellement, Organisme de formation. Cette étape ne correspond qu'à l'explication des points concernés par le plan de cours de DEMOS intitulé Access : Les Bases, que vous pourrez retrouver dans la section informatique.
J'aimerais que vous considériez ce manuel comme un véritable cours. Je vais vous expliquer, calmement, mais sûrement chaque étape de la réalisation d'une base de données sous Access.
L'idéal, pour construire une base de données est de connaître la méthode Merise. Si vous décidez de faire de la conception/réalisation de bases de données votre métier, commencez par cela. Sinon, les méthodes que je vais vous donner par la suite devraient être suffisantes pour faire face à la majorité des cas.
Quelques liens utiles :
Quelques références :
Pour comprendre l'importance des bases de données, prenons un exemple. Ne serait-il pas plus simple de saisir les commandes dans Excel, pour permettre leur suivi et d'utiliser Word pour faire un mailing pour les imprimer et de les envoyer à mes clients, que de se mettre à un logiciel tel qu'Access ?
Examinons les faits. Si nous voulons faire un tableau dans Excel à cette fin, nous nous dirigerons vraisemblablement vers quelque chose de semblable à ceci (j'ai pris soin de masquer quelques colonnes pour pouvoir tout afficher à l'écran) :
Nous avons bien l'ensemble des informations relatives à notre client, toutes celles propres à la commande de ce client, ainsi que tout ce qui concerne les produits commandés.
Nous saisissons la première ligne de notre première commande, et tout va pour le mieux.
MAIS, notre client n'a pas commandé qu'un seul produit. Et là, intervient le premier souci : qu'allons-nous faire du deuxième produit ? Nous allons l'écrire sur la ligne du dessous, en prenant bien soin de recopier chaque information relative au client et à la commande car, sans cela, le moindre tri risque d'être fatal. Nous risquons de perdre la relation existant entre mon produit et ma commande, donc mon client…
Cela nous oblige à dupliquer la plupart des informations que nous avons déjà saisies, mais, quoi qu'il en soit, nous pouvons donc arriver à ce résultat, qui nous donnent une certaine satisfaction :
A partir de là, d'autres soucis peuvent intervenir : Comment savoir combien de commande sont enregistrées ? Nous ne pouvons plus compter les lignes. Certaines commandes feront une seule ligne, d'autres cinquante (nous l'espérons). Nous serons donc amené à faire un traitement d'extraction pour récupérer chaque N° de facture sans ses doublons, pour ensuite compter le nombre de lignes ainsi récupérées. Mais cela reste faisable avec Excel.
Puis les commandes s'enchaînent et nous en arrivons à nous poser des questions d'analyse. Par exemple, nous aimerions, partant du tableau suivant :
Connaître le CA pour le produit TOMATES. Combien cela atteint-il ? Il est vrai qu'il est très simple d'utiliser Excel pour faire des calculs. Il est fait pour cela. Mais en l'occurrence, le résultat renvoyé par Excel sera de 239€ et non de 355€ car, lors de ma saisie, j'ai fait une erreur : une ligne indique TOMATES et l'autre TOMATE. Or pour Excel, il y a une grosse différence entre TOMATE et TOMATES. Cela pour indiquer que, même si la recopie est un outil aisé, il n'en reste pas moins source d'erreurs. Nous ne parlons même pas de la limite physique de ce tableau qui ne pourra jamais excéder les 65535 lignes. Et, dans ce cas, les temps de latence seront extrêmes, notamment dans le cas de calculs analytiques complexes.
En résumé, même si on peut s'en sortir par la technique Excel, de nombreux problèmes peuvent venir s'interposer :
Il faut donc trouver un autre système. C'est là qu'interviennent les Bases de Données Relationnelles.
Un SGBDR est un ‘Système de Gestion de Bases de Données Relationnel'.
Examinons ce nom dans le détail :
Access est un ‘Système de Gestion'. C'est un logiciel, et en aucun cas, il ne s'agit d'une base de données ! C'est un ‘Système' qui sert à ‘Gérer', et à ‘Gérer' des ‘Bases de données Relationnelles'. La ‘Base de Données Relationnelle' est donc le type de fichiers gérés par Access, au même titre que Word gère des documents et Excel des classeurs.
Sur Access, les documents gérés ont une extension en .mdb (ou .mde) . Il s'agit des bases de données accessibles à travers le moteur de base de données utilisé nativement par Access, moteur qui porte le doux nom de JET.
RAD est l'acronyme de ‘Rapid Application Development'
Grâce à Access, vous pourrez effectivement très rapidement développer des applications complètes, stockage de données et interface compris dans le même fichier, par exemple.
La conception de la base est l'étape fondamentale.
Si nous voulons reprendre l'exemple de la construction de la maison, il s'agit tout simplement de la fabrication des plans ! Si les plans sont vite faits, sans concertation avec les différents intervenants, sans prendre en considération le terrain, etc. il parait inconcevable que la maison soit bâtie sans soucis !
En fait, le plan va être réalisé par un architecte, basé sur une étude du terrain réalisée par un géomètre, en accord avec la mairie, les bâtiments de France (si nécessaire) , etc.
De même, pour réaliser la conception de votre base de données, vous allez devoir prendre des informations auprès des différentes sources concernées, auprès des utilisateurs, des décideurs, faire des réunions régulières pour savoir si ce que vous concevez est bien en adéquation avec la réalité bref, c'est un travail qui demandera un temps certain. Ne vous précipitez donc pas dans cette étape de la réalisation de votre base de données. Une fois la conception faite, le reste ressemblera à une partie de plaisir.
Repartons de notre cas précédent, réalisé sur Excel, qui nous posait quelques soucis : celui des commandes.
L'idée est de réussir à rassembler les informations, comme sur des fiches types.
Par exemple, un client est un client. Quel que soit le client, nous allons systématiquement avoir besoin d'informations qui sont propres à chaque client, comme l'adresse, le nom, le téléphone, etc. Nous pouvons gérer ce client, l'enregistrer sur une fiche. D'ailleurs, ne parle-t-on pas de fiche client, et même, de fichier client.
Nous pourrions même imaginer ‘préformater' des feuilles ainsi, nous n'aurions plus qu'à les remplir et à les mettre dans notre fameux ‘fichier client'.
En fait, cette ‘fiche préformatée', on pourrait l'appeler ‘entité'. Tout le jeu va donc être, maintenant, de répartir les données que nous avons dans notre tableau de départ, et de les rassembler par ‘fiche type' et de faire autant de ‘fiche type' ou ‘entité' que nous avons d'éléments distincts à gérer. C'est le point de départ du Modèle Entité-Association (MEA)
Le modèle entité-association est un modèle, un dessin qui représente les différents éléments (entités) et leurs interactions (associations) , dans le système qui nous intéresse.
Cette définition est peut-être un peu vague. Aussi, je vous propose d'avancer pas à pas dans la méthode rapide que je vous propose.
Sur notre problématique, nous avons repéré trois entités : Client, Commande et Produit.
Nous pourrions les dessiner ainsi :
Chose importante, dans chaque entité, nous devons créer une zone qui contiendra une valeur obligatoire et unique à travers toutes les fiches tirées de cette entité. On appelle cela un identifiant. Voici la définition la plus correcte de l'Identifiant : "L'identifiant assure l'unicité de l'occurrence (de la fiche remplie) de l'entité". Dans le cas du client, ce sera le N° de client. Pour la commande, le N° de Commande. Pour les produits, la référence produit fera l'affaire (un code barre par exemple). Après avoir ajouté nos identifiants, nous obtenons ceci (vous noterez la convention qui consiste à souligner l'Identifiant) :
Maintenant, on va pouvoir ajouter toutes les informations (on les appelle 'attributs') que l'on souhaite enregistrer dans chaque fiche. Le résultat obtenu doit ressembler à ceci :
Nous avons maintenant une série de fiches type, et nous sommes capables, dans chaque fiche, de stocker toute l'information qui nous intéresse.
De plus, comme les identifiants sont uniques, on peut très facilement retrouver un client, une commande ou un produit, simplement par son identifiant. Enfin, rien ne nous empêchera d'avoir deux clients ayant le même nom mais étant localisés dans des villes différentes par exemple. Nous ne risquons pas de les mélanger, puisqu'ils ont des identifiants différents.
Cependant rien ne nous permet de déterminer ce qui se passe entre ces fiches type. Par exemple, si nous lisons bien la fiche d'une commande, nous n'avons aucun moyen de savoir quel client l'a passée. Nous avons donc un peu de mal à exploiter les fiches en l'état actuel des choses. Il va falloir déterminer les actions se passant entre les fiches, et savoir en garder trace. C'est tout l'objet de la deuxième étape.
L'association sert à définir l'action qu'exercent les entités entre elles. C'est pour cela qu'on les désigne souvent par un verbe.
Que fait le client ? Il passe des commandes. Il existe donc une action que fait le client sur les commandes, et cette action est décrite par le verbe ‘passer'. On peut donc tracer l'association entre le client et la commande comme ceci :
Maintenant, on peut aussi dire que les commandes regroupent des produits. Encore une fois, le verbe regrouper décrit ici l'action qui se passe entre les commandes et les produits.
Cette étape importante étant terminée, il faut déterminer COMMENT ces associations s'exercent. Il s'agit de déterminer les cardinalités.
Pour cela, on se sert d'une petite phrase magique que voici :
« Est-ce que un(e) {ENTITE} peut {ACTION} plusieurs {AUTRE ENTITE} »
Application : Occupons-nous de la relation ‘passer‘ entre Client et Commande, et posons-nous cette question de l'une des entité vers l'autre, puis vice-versa :
« Est-ce que un {Client} peut {passer} plusieurs {Commandes} »
La réponse est OUI, bien sûr, et heureusement !
« Est-ce que une {Commande} peut {être passée par} plusieurs {Clients} »
Cette fois la réponse est NON. Chaque commande n'est passée que par un seul client.
Comme nous ne pouvons donner qu'une seule réponse positive sur les deux question, nous avons une association 1-n (un à plusieurs).
A partir de là, tout est simple.
Lorsque nous sommes en présence d'une relation un à plusieurs, il faut reproduire l'identifiant de l'entité côté 1 dans l'entité côté plusieurs :
Et là, la raison devient évidente :
Si nous lisons la fiche commande, nous voyons que la commande dont le numéro est 12345, a été passée à une date donnée, livrée à une autre date précise, chez un destinataire clairement identifié. Mais maintenant, apparaît aussi sur cette fiche le numéro du client (C987) qui a passé la commande. Et si l'envie nous prenait de savoir de quel client il s'agissait, il suffirait alors d'aller trouver, parmi les fiches des clients, la fiche dont le numéro est C987. Ce numéro étant unique parmi toutes les fiches, nous sommes certains qu'il ne pourrait s'agir QUE de Dupont & Co.
Passons maintenant à la deuxième association, l'association ‘regrouper'
« Est-ce que une {Commande} peut {contenir} plusieurs {Produits} »
La réponse est OUI, bien sûr, et heureusement !
« Est-ce que un {Produit} peut {être contenu dans} plusieurs {Commandes}»
Cette fois encore la réponse est OUI. Il est évident que le produit TOMATE peut apparaître dans plusieurs commandes.
Comme nous pouvons donner deux réponses positives, nous avons une association n-n (plusieurs à plusieurs).
Dans ce cas, le traitement va se faire en plusieurs phases.
Tout d'abord, nous allons reproduire les 2 identifiants dans l'association (qu'il va falloir agrandir pour l'occasion). Nous obtenons donc ceci :
Mais surtout, il va falloir penser à toutes les informations que nous n'avons pas pu stocker jusqu'à présent. Par exemple, si nous nous référons au tableau Excel que nous avions fait au départ, et le comparons aux données présentes dans nos entités, on peut se demander ce qu'il est advenu des quantités !
En réfléchissant, on comprend bien que ces quantités ne peuvent être présentes dans les produits, car il ne s'agit pas de ‘quantité de produits' mais de ‘quantité de produits dans les commandes'. De même pour l'entité des commandes. En aucun cas il ne s'agit de la quantité de commandes n'est-ce pas !
La place de cette valeur est donc maintenant toute trouvée : dans l'association ‘Regrouper'. Maintenant, si je faisais ceci, que penseriez-vous :
Certains diront certainement que, vu que nous cherchons à éviter la duplication des données, il est inutile de reproduire le PrixUnitaireHT et la TVA qui sont déjà présents dans l'entité Produit, mais que la Quantité et la Remise sont bien placées.
Partagez-vous ce point de vue ?
C'est probable. Pourtant, même si ces informations ont le même nom, elles n'emportent pas la même signification :
Les PrixunitaireHT et TVA de l'entité Produit ont rapport avec le prix et le taux actuels. Ceux qui sont appliqués pour la facture qu'on est en train de faire.
Les PrixUnitaireHT et TVA de l'association Regrouper ont, quant à eux, rapport avec les données historiques. Chaque ligne de commande étant enregistrée ici, nous gardons une trace de l'historique des prix. Ce que nous ne trouvons nulle part ailleurs. Nous avons donc aussi bien les prix des commandes de Janvier 2003 que ceux d'avril 2004. Et sans ces données, nous serions dans l'impossibilité de calculer l'évolution du chiffre d'affaire, car le seul prix et le seul taux disponibles concerneraient l'instant présent.
Donc, lorsque nous sommes en présence d'une association plusieurs à plusieurs, il convient de toujours se poser les questions relatives aux données complémentaires, et tout particulièrement les données historiques.
Après avoir posé la question magique, vous retiendrez 3 situations au maximum. Nous n'avons pas encore parlé de la troisième, mais nous y viendrons plus tard… beaucoup plus tard tellement elle est rare :
| un-à-plusieurs | plusieurs-à-plusieurs | un-à-un |
|
Ce cas est extrêmement courant. Il est très facilement géré également.
- dans ce cas, il faut reproduire l'identifiant de l'entité qui est du côté un dans l'entité côté plusieurs (à noter que le nom même est peu important, mais que l'information qu'on va y mettre, elle, l'est) |
Ce cas aussi est très fréquent. Il est un peu plus complexe à mettre en œuvre. Il correspond en fait à 2 associations un-à-plusieurs
- reproduire les identifiants des deux entités dans l'association - Réfléchir à l'ensemble des données annexes (y compris historiques) |
Ce cas est extrêmement rare. Si une entité ne peut être liée qu'à une seule autre entité et réciproquement, il est fort probable qu'il s'agisse en fait de la même entité |
Nous allons énoncer 2 exercices. Essayez de les réaliser avant de passer aux chapitres suivants.
Exercice 1 : Commandes
En partant de notre modèle de prise de commande précédent, ajoutez les entités suivantes :
Essayez de réaliser une solution par vous-même. Vous pourrez retrouver la correction de cet exercice en annexe A du présent manuel, à l'entrée ‘Solutions du MEA 1'
Exercice 2 : Société Informatique
Nous allons passer maintenant à la mise en application. Voici un petit exercice que vous avez essayé de résoudre par vous-même. Je commence par mettre en situation.
Imaginez que vous interveniez pour une société de prestations de services informatiques, afin de concevoir une base de données permettant le suivi et la facturation des interventions réalisées chez les clients. La société est susceptible de réaliser n'importe quelle intervention. Il faut impérativement que le modèle que vous allez concevoir puisse répondre aux questions suivantes, sans en oublier une partie :
Voici quelques informations supplémentaires que vous devez connaître :
Essayez de réaliser une solution par vous-même. Vous pourrez retrouver la correction de cet exercice en annexe A du présent manuel, à l'entrée ‘Solution du MEA 2'
Les tables sont les structures, des tableaux en fait, un peu sur le même principe que les feuilles de calcul d'Excel, qui servent uniquement au stockage des données.
Attention ! Même si certains logiciels tels SQL Server permettent le stockage de données calculées, ce n'est pas le cas d'Access. Dans Access, seules des données statiques peuvent être conservées. Il n'y a pas de possibilité, à ce jour, de mettre des données dynamiques dans les colonnes des tables.
Si vous avez utilisé un logiciel de modélisation de base de données tel que , il sera très aisé de créer aussi bien les tables que les relations. En effet, la plupart des logiciels de modélisation permettent une génération de script qu'il suffira d'exécuter pour générer la base de données.
Ces logiciels de modélisation vous permettent de rapidement modéliser votre modèle. Ainsi, le résultat de votre exercice de modélisation aurait donné quelque chose comme ceci :
Ensuite, en quelques clics, CaseStudio vous génère un script dans une petite fenêtre :
Une fois le script de généré, il vous suffit de suivre les indications fournies dans ce dernier :
Vous obtiendrez alors une base avec toutes les tables préparées, les relations faites, et tout cela sera consultable directement dans la fenêtre des relations comme cela est montré dans l'image suivante (j'ai un peu réorganisé les tables pour que cela paraisse plus clair) :
Toutes les tables et les relations ont été générées !
Et cela n'a pris que quelques secondes.
Si vous envisagez de travailler souvent à la modélisation de bases de données avec Access, je ne peux que vous recommander l'acquisition d'un tel logiciel.
Toutefois, la création d'une base de données de temps en temps ne nécessite pas ces outils, et Access nous permet très facilement de créer des tables, une fois notre modèle réalisé. Voyons maintenant comment.
Il n'y a rien de plus facile que de créer une table une fois le modèle physique de données correctement défini.
Dans le modèle que nous avons généré précédemment, prenons la table des services. Que pouvons-nous observer ?
Il y a trois informations : l'identifiant, le nom, le lieu. Ces trois informations sont des champs. Les identifiant et la clé primaire. Il va nous falloir reproduire très précisément ces mêmes éléments.
Positionnons-nous dans la liste des tables.
Cliquons sur le bouton nouveau.
Parmi tous les choix qui nous sont proposés, choisissons le mode création.
Nous obtenons l'interface suivante :
Vous noterez avec intérêt la colonne ‘Nom de Champ'. Il s'agit en fait d'un tableau, et sur chaque ligne de ce tableau il est maintenant possible de définir un nom de champ. Nous arrivons donc rapidement à ce résultat :
Les trois champs étant définis, il nous faut maintenant indiquer que le champ identifiant est bien la clé primaire. Nous nous positionnons sur ce dernier. Et nous pouvons cliquer sur l'icône de la clé primaire ce qui a pour effet de faire apparaître une petite clé dans le sélecteur situé à gauche de notre champ. Voilà, nous avons défini une clé primaire.
Il ne nous reste plus qu'à enregistrer cette table. Cela s'effectue comme dans tous les logiciels de Microsoft, en cliquant sur la petite disquette.
Appelons cette table tblServices (je préfixe le nom donné table systématiquement par le trigrammes tbl).
Nous venons de finir de définir la structure de notre table. Pour voir l'aspect 'Feuille de Données' de cette table, il vous suffit de cliquer sur le premier bouton de la barre d'outils.
Nous pouvons maintenant fermer cette table. Nous venons de créer notre première table.
Vous avez constaté comme il est facile de créer une table. Cependant, Access à aussi des fonctionnalités avancées qui vont vous permettre d'affiner les possibilités de saisie des informations dans votre table.
Il y a plusieurs méthodes pour créer une table. Vous les avez vues lorsque vous avez cliqué sur le bouton Nouveau Si ces autres modes ne nous intéressent pas ici, je vais malgré tout rapidement vous les présenter :
J'ai l'habitude de comparer la bascule des modes d'affichage à une manipulation que vous avez tous déjà faite. Prenez une pièce de monnaie. Vous en voyez le côté face. Si vous voulez voir le côté pile, il vous sera inutile de remettre la pièce dans la poche pour cela, il suffira de la retourner. Il en est de même pour votre table. Il y a deux affichages possibles, au même titre qu'il y a deux faces sur une pièce. Il sera inutile de fermer la table pour en changer l'affichage, le premier bouton à gauche de votre barre d'outils (quelle qu'en soit l'image) servant à cette opération. Ce bouton (le premier de la barre d'outils) est à retenir, car tous les objets que nous verrons dans Access auront deux affichages possibles, et à chaque fois, pour basculer du mode d'affichage 'Structure' au mode d'affichage 'Données', c'est ce bouton que nous pourrons utiliser.
Je vous propose, dans la partie suivante, d'essayer de réaliser la table des employés, mais en parcourant la totalité des fonctionnalités avancées sur les champs, aussi bien que sur les tables.
Commençons comme pour la création de la table précédente :
Nous voici donc arrivés à un résultat qui devrait être semblable à celui-ci :
Pour l'instant, nous ne nous étions intéressés qu'à la première colonne intitulée 'Noms de Champs'. Passons aux deux colonnes suivantes. 'Type de Données' et 'Commentaires'.
Vous pouvez écrire ici tout texte vous permettant de vous rappeler très précisément ce que vous vouliez mettre à l'origine dans votre champ. Cependant, cette zone a un intérêt particulier pour l'utilisateur final. En effet, tout texte inscrit dans cette zone apparaitra dans la barre d'état de l'application. Par exemple, nous souhaitons que l'utilisateur saisisse le nom de famille en lettres majuscules. Le champ empNom pourra donc être commenté comme suit :
L'effet donné en mode feuille de données sera celui-ci :
Cette zone est facultative. Cependant, si vous voulez un conseil utile, considérez qu'il est obligatoire. Il vous permettra certainement d'économiser des heures de travail si vous avez à revenir modifier votre base de données dans quelques mois. Si nous prenons le temps de remplir chaque zone de commentaire, nous arriverons à ce résultat :
Intéressons-nous à la dernière colonne : Types de Données.
Je dois bien avouer qu'ici, je ne partage pas pleinement le choix fait par Microsoft, au niveau du découpage des types. Même si cela est fait pour que tout un chacun puisse travailler rapidement avec Access, il est toujours bon de savoir avec quoi nous travaillons dans nos champs ou colonnes, au sein d'Access.
Aussi, j'aimerai attirer votre attention sur ce petit schéma qui vous permettra certainement de mieux vous situer :
Il y a trois grands Types, et une multitude de Sous-types.
Voici maintenant quelques explications
Ces types de données contiennent toutes les séries de caractères générés depuis le clavier, ne devant pas être interprétés comme des numériques.
| Type | Description |
| Texte(N) | N correspond au nombre maximal de caractères que vous pouvez saisir dans la colonne. Ce nombre est compris entre 1 et 255. Ce type est le plus utilisé pour tout ce qui concerne le stockage de données caractères. Il est aussi le plus pratique. Vous noterez certainement que le seul élément dans la liste est Texte. La longueur du texte, le Nbre, est défini dans la propriété 'Taille du Champ'. |
| Mémo | Il n'est pas très propre de mettre mémo dans cette catégorie. Cependant, j'ai choisi de le mettre ici, car il concerne également uniquement les caractères, au même titre que le type de données Texte(Nbre). |
| Lien HyperTexte | Il s'agit de texte, stockés comme tels, mais utilisés comme liens hypertexte. |
Vous vous souvenez certainement de ces tortures infligées par les professeurs de mathématiques pendant les cours de 6°, 5°, 4° etc. Et bien on va reprendre là Plus précisément, nous allons nous intéresser particulièrement aux ensembles N er R vous vous vouvenez maintenant ?
Les Nombres Entiers (N)
| Type | Description | Taille |
| Octet | Utilisé pour stocké de petits nombres entiers positifs compris entre 0 et 255 | 1 octet |
| Entier | Ce type permet de stocker des nombres entiers positifs et négatifs compris entre -32768 et 32767 | 2 octets |
| Entier Long | C'est le plus grand type de données pour des nombres entiers. On pourra stocker des nombres compris entre -2147483648 et 2147483647 | 4 octets |
Les Nombres Réels (R) (Pour rappel, ces nombres sont décimaux )
| Type | Description | Taille |
| Réel Simple | Utilisé pour stocké de petits nombres entiers positifs compris entre 0 et 255 | 1 octet |
| Réel Double | Ce type permet de stocker des nombres entiers positifs et négatifs compris entre -32768 et 32767 | 2 octets |
Créer une requête basée sur la table client
Afficher le champ [Société]
En SQL on aurait :
SELECT Société FROM Clients; |
Quel est le résultat attendu ?
Aucun traitement particulier n'est effectué sur cette requête. Il devrait donc s'agir de l'affichage brut de la liste des champs [société] de la table des clients. Le nombre de lignes du résultat de la requête devrait donc correspondre au nombre de lignes dans la table.
Quel est le résultat obtenu ?
Nous obtenons ici quatre-vingt-onze lignes.
Que pouvons-nous en déduire ?
Qu'il y a 91 clients. Pour s'en assurer il suffit de regarder le contenu de la table Clients.
Dans la requête précédente ajoutez la table commande. Nous allons maintenant examiner ce qui se passe lorsqu'on met dans une requête des tables qui ne servent à rien pour obtenir le résultat attendu.
Notez au passage la description automatique de la relation existante entre la table des clients et celle des commandes. ACCESS reprend ici l'ensemble des informations définies préalablement dans la fenêtre des relations. Il s'agit d'une relation un à plusieurs, telle que un client peut avoir plusieurs commandes, chaque commande n'ayant qu'un seul client.
Quel est le résultat attendu ?
En SQL on aurait :
SELECT Société FROM Clients INNER JOIN Commandes ON Clients.[Code Client]=Commandes.[Code Client]; |
Puisqu'on continue à n'afficher que le champ [société], on s'attend logiquement à obtenir la liste des sociétés ayant des commandes, soit nos 91 lignes trouvées précédemment.
Quel est le résultat obtenu ?
Nous obtenons maintenant 830 lignes.
Que pouvons-nous en déduire ?
La réponse qui nous vient à l'esprit immédiatement est : les 91 clients ont effectué 830 commandes.
Cependant, cette affirmation n'est pas obligatoirement exacte. Pourquoi ? Parce que la relation entre les 2 tables est qualifée de jointure équivalente. C'est-à-dire que cette requête travaille uniquement sur l'ensemble des associations possibles entre le champ [code client] de la table des clients et le champ [code client] de la table de commandes. Mais se pourrait-il qu'il y ait des commandes n'ayant pas de clients référencés dans la base ? N'oublions pas la présence de l'intégrité référentielle dans cette relation. Comme nous l'avons vu précédemment, elle permet de s'assurer que pour une clé étrangère il y a toujours une clé primaire qui lui corresponde. On peut donc dire qu'il y a effectivement 830 commandes car nous sommes certains qu'il n'y a que des commandes liées à des clients.
En regardant attentivement le résultat de la requête, nous notons que le nom de la société apparaît à plusieurs reprises. Cela montre le nombre de relations possibles entre le client et la commande. Le nom de la société apparaît autant de fois que cette société a passé des commandes.
Attention ! Notez bien ceci :
Si l'intégrité référentielle n'avait pas été activée sur cette relation, ce nombre de 830 lignes ne pourraient pas signifier autre chose que 830 associations client-commandes. Il serait en effet possible qu'il y ait, dans la table des commandes, des commandes ayant des codes client n'existants pas dans la table des clients.
Admettons que nous cherchions à obtenir à nouveau les quatre-vingt-onze lignes de l'exercice numéro un, il semble que ce soit la relation qui nous empêche d'aboutir. La logique immédiate voudrait qu'on supprime cette relation. Alors supprimons-la et voyons ce qui se passe.
Quel est le résultat attendu ?
Nous voudrions récupérer les quatre-vingt-onze lignes du départ. En effet, la grille indique que nous n'affichons que le champ [société] de la table des "clients".
En SQL on aurait :
SELECT Société FROM Clients, Commandes |
Quel est le résultat obtenu ?
Il y a cette fois 75 530 lignes.
Que pouvons-nous en déduire ?
Que, sauf cas exceptionnel, il faudra toujours veiller à ce qu'il y ait des relations entre les différentes sources d'une requête. L'absence de l'une d'entre elles suffirait à réaliser un produit cartésien, c'est-à-dire l'association de chaque enregistrement d'une table à tous les enregistrements de l'autre table.
Ici ACCESS, n'ayant aucune indication des relations qu'il doit faire entre les clients et les commandes, va associer chacun des 91 clients avec les 830 commandes. Nous obtenons ainsi 91 X 830=75 530 lignes.
Cela ne correspond pas aux résultats attendus. Nous allons donc restaurer notre requête dans l'état précédent en supprimant la table commande et en la rajoutant à la requête. Nous nous retrouvons donc ici exactement dans le même état qu'à la fin de l'exercice numéro deux.
Nous n'avons toujours pas réussi à récupérer les quatre-vingt-onze lignes du départ. En observant attentivement le résultat, comme nous l'avons dit précédemment, nous observons que le nom de la société apparaît plusieurs fois. Si nous parvenions à regrouper en une seule ligne toutes les occurrences d'un même nom de société, nous obtiendrions vraisemblablement les quatre-vingt-onze lignes attendues.
Faisons donc un clic sur le bouton sigma de notre barre d'outils. Cela fait apparaître une nouvelle ligne dans la grille. Il s'agit de la ligne opération.
Nous observons que l'opération sélectionnée est l'opération de regroupement.
Quel est le résultat attendu ?
En SQL on aurait :
SELECT Société FROM Clients INNER JOIN Commandes ON Clients.[Code Client]=Commandes.[Code Client] GROUP BY Société; NB : Une autre solution aurait été d'utiliser la Clause DISTINCT afin de n'afficher que les résultats uniques, ce qui aurait modifié le SQL ainsi : SELECT DISTINCT Société FROM Clients INNER JOIN Commandes ON Clients.[Code Client]=Commandes.[Code Client]; |
ACCESS devrait essayer de regrouper toutes les sociétés identiques pour n'en faire qu'une seule ligne, et nous devrions ainsi récupérer nos quatre-vingt-onze lignes du départ.
Quel est le résultat obtenu ?
Nous obtenons cette fois seulement 89 lignes.
Que pouvons-nous en déduire ?
Premièrement, ayant regroupé sur les noms de société, il n'est pas impossible que nous ayons récupéré en une seule ligne au moins deux sociétés différentes de même nom. Il s'agit donc de s'assurer que chaque client apparaît bien dans sa ligne. Pour cela, rien ne vaut le regroupement sur une valeur unique et facilement identifiable, l'identifiant ou clé primaire
Rajoutons donc dans notre requête le champ [code client] de la table clients. L'opération est encore une fois l'opération de regroupement. Il s'agit en effet de l'opération par défaut. Décochons la case afficher, car, si le regroupement est utile, l'affichage du code ne nous est pas indispensable pour cet exercice.
Quel est le résultat obtenu ?
Cette fois, ACCESS devrait essayer de regrouper les identifiant des clients ainsi que leurs sociétés, de manière à créer des lignes dont l'association code client-société soit unique. Par conséquent, si plusieurs sociétés avaient le même nom, on devrait voir apparaître autant de lignes qu'il y a des clients différents, qu'ils aient ou n'aient pas le même nom de société.
Nous obtenons toujours 89 lignes.
Que pouvons-nous en déduire ?
Cette fois nous sommes absolument certains qu'il s'agit bien de 89 clients différents.
Comme nous l'avons décrit à l'exercice numéro deux, le résultat de la jointure permet d'obtenir l'ensemble des associations possibles entre la table des clients et celle des commandes. Nous avons également déterminé avec certitude qu'il ne pouvait pas y avoir de commandes sans client. Cependant il est tout à fait probable qu'il existe des clients n'ayant passé aucune commande. Conscient de cela, et au vu des points examinés précédemment, nous en arrivons à la conclusion logique que nous avons 2 clients n'ayant pas passé de commandes.
Étant donné le résultat que nous avons obtenu à l'heure actuelle, comment faire pour obtenir les quatre-vingt-onze lignes que nous avions au départ ? Le problème est ici le type de jointure qui est une jointure équivalente. Les associations ne sont que les associations permettant de joindre deux champs identiques.
Il existe plusieurs types de jointure sous ACCESS :
Examinons la réaction de chacune d'entre elles.
Imaginons deux tables dans la plus simple expression : quelques champs, pas de clé primaire, pas d'index. Le champ de liaison est un champ de type texte un seul caractère. Traçons une relation du champ de liaison de la table un vers le champ de liaison de la table deux. Nous dirons ainsi que la table de gauche est la table un, et que la table droite est la table deux. En effet, le côté gauche est le point d'origine de la relation, et le côté droit, de son point d'arrivée. (Cette notion de gauche et droite n'a aucun rapport avec la position des tables dans la requête. Où seraient la gauche et la droite si les deux tables étaient l'une sur l'autre ? Tout dépend du tracé de la relation. Le côté gauche de la jointure est le point d'origine de la relation ; le côté droit son point d'arrivée.) Nous obtenons le schéma ci-dessous :
Lorsque la requête va s'exécuter, elle cherchera à travailler sur le tableau résultant de cette relation à savoir l'association de tous les champs équivalents. Cela donnera la table suivante :
Nous voyons bien que certains points de ces deux tables ne sont pas pris en compte dans la table de résultats. Par exemple comment faire pour qu'apparaissent dans la table de résultats tous les champs de la table un, donc de la table gauche. Il faut changer le type de jointure et la transformer en une jointure gauche. La table résultant d'une requête avec une jointure gauche sera semblable à la table suivante :
Dans ce type de jointure nous obtenons une jointure équivalente plus tous les enregistrements orphelins (qui n'ont pu être liés) de la table de gauche, associés à des champs NULL.
Qu'en est-il de la jointure droite ? Il s'agit de l'inverse de la jointure gauche. Si la jointure que nous avions mise en oeuvre était une jointure droite la table résultant d'une telle requête serait la suivante :
Cette fois, ce sont des champs de la table gauche que nous ne voyons plus. La jointure droite correspond à une jointure équivalente à laquelle on ajoutera tous les champs orphelins de la table droite, liés à des champs NULL.
Modifions le type de jointure.
Pour cela, il suffit de faire un double-clic sur la relation. Apprait alors la fenêtre suivante :
Les 3 points mentionnés sont dans l'ordre de l'exposé ci-dessus :
Choisissons le point N°2 (la jointure gauche) de manière à pouvoir avoir l'ensemble des clients, avec et sans commandes. Dans la grille d'Access, le résultat sera le suivant :
En SQL on aurait :
SELECT Société FROM Clients LEFT JOIN Commandes ON Clients.[Code Client]=Commandes.[Code Client] GROUP BY Société; |
Dans notre cas si nous voulons visualiser tous les clients, même ceux n'ayant pas passé de commandes, il faudra prendre la jointure gauche. En effet, la relation ayant été tracée depuis la table clients vers la table commandes, la table clients est la table de gauche. Puisque nous voulons tous les clients, il faudra faire une jointure gauche (Rappel : pour modifier le type de jointure, il suffit de faire un double-clic au milieu de la relation).
Quel est le résultat obtenu ?
Nous obtenons cette fois les quatre-vingt-onze lignes attendues.
Que pouvons-nous en déduire ?
Que nous avons fini par récupérer les enregistrements de la table clients qui ne sont pas liés à la table commandes, tout en conservant chaque occurrence des clients ayant des commandes.
Maintenant que nous avons réussi à récupérer les 91 clients du départ la question qui se pose est : "peut-on ne récupérer que les clients n'ayant pas passé de commandes ?".
La réponse est oui. Si nous revenons sur le tableau vu dans l'exercice précédent, nous observons qu'il est facile d'identifier les enregistrements dont les jointures ne sont pas équivalentes. Dans notre cas lorsque les enregistrements joints n'ont pas de données équivalentes dans la table de côté commandes, ils sont tous mis à NULL. Si ce sont ces derniers que nous cherchons à récupérer, il suffit de déterminer un critère NULL sur un champ qui ne devrait pas l'être (par exemple la clé externe ou un identifiant, une clé primaire).
Dans notre cas nous allons descendre dans notre requête, le Code Client qui est la clé étrangère de la table commande.
Quel est le résultat attendu ?
En SQL on aurait :
SELECT Société FROM Clients LEFT JOIN Commandes ON Clients.[Code Client]=Commandes.[Code Client] WHERE Commandes.[Code Client] Is NULL; |
Nous devrions obtenir deux lignes contenant le nom de la société.
Quel est le résultat obtenu ?
Nous obtenons les 2 lignes clients attendues. Ces dernières correspondents aux deux clients n'ayant pas passé de commandes.
Nous venons de créer une requête de non correspondance.
Cette petite série d'exercices vous aura permis certainement de toucher du doigt la subtilité des types de jointure, et l'importance des relations dans une requête.
Avez-vous bien compris ?
Je vais vous donner ici l'occasion de vous tester. Essayer de réaliser la demande suivante :
Je souhaite avoir un tableau me permettant de suivre les mouvements attachés à mes fournisseurs. Certains Fournisseurs (au sens 'enregistrements de la table fournisseurs') sont peut-être enregistrés dans la table Fournisseurs, mais, pour le moment du moins, je n'ai référencé aucun de leurs produits.
Il est aussi possible que, sur la quantité des produits référencés dans la base, certains ne se vendent pas du tout. Auquel cas, en poussant loin, il est possible d'imaginer un Fournisseur ayant des produits référencés, et pourtant, sur ce fournisseur, je ne génère aucun Chiffre d'affaires.
J'aimerai donc que vous essayez de construire la requête suivante :
Explication de chaque colonne retournée :
Bon et bien maintenant, c'est à vous.
Il m'est avis que vous ne pourrez pas réaliser cela en une seule requête.
PS : dernier 'petit truc' : Pour tester que votre requête fonctionne, ajoutez donc un fournisseur ; il devrait apparaitre même s'il n'y a pas de produit. Puis recommencer l'expérience sur un nouveau produit. En effet, actuellement, tous les produits sont commandés. il n'y a donc pas d'écart entre NbProds et NbCommandés.
Prenez votre temps de toutes façons, la solution est en annexe A.
Au vu du nombre d'informations que je vous ai donné il y avait plusieurs solutions. Aussi, nous allons détailler chaque cas, et prendre une solution, faire des choix, afin de trouver un modèle qui nous servira ultérieurement.
C'est certainement, le point sur lequel je préfère discuter. Repartons de notre modèle de base :
A ce modèle, il faut maintenant ajouter une Entité, l'entité Messager. Sachant que le messager est celui qui s'occupe des livraisons, comme indiqué dans l'énoncé, on aurait immédiatement tendance à modéliser comme suit :
Lier l'entité Messager avec l'entité Commande, créer une association livrer, et, grâce à la petite phrase magique :
«Est-ce que un {Messager} peut {Livrer} plusieurs {Commandes}» => Oui
«Est-ce que une {Commande} peut {être livrée} par plusieurs {Messagers}» => Non
On en déduit une association de 1-n, de Messager vers Commande, et donc, on reproduit l'identifiant de l'entité Messager dans l'entité Commande.
Cette solution, est, à priori satisfaisante. Pourtant, à l'examiner de plus près, on peut en tirer plusieurs informations quant au fonctionnement de l'entreprise que vous êtes en train de modéliser. Ainsi, cette société envoie ses commandes en une seule fois ! Pour le comprendre, je vais vous donner un exemple :
Imaginez que, dans une commande, un client souhaite recevoir 20 Kg de Patates, mais, vous n'en avez que 5 en stock. Votre modélisation vous empêche de livrer d'abord les 5 Kg, pour livrer le reliquat ultérieurement, en effet, nulle part vous n'avez la possibilité de stocker l'information relative à la quantité déjà livrée, et donc, d'en déduire la quantité de produit restant à livrer, ce fameux reliquat. En fait, l'association relie bien les Commandes et les Messagers par l'action Livrer, donc un Messager va effectivement Livrer des Commandes, et non des Produits, ce qu'il est absolument nécessaire de faire dans la mesure où l'on souhaite faire un suivi précis de ce qui est livré dans la Commande. En fait, pour y arriver, il aurait fallu lier Messager avec l'association existante Regrouper.
Il aurait fallu raisonner comme suit :
Puisque je souhaite un suivi des lignes de commande, nous avons fait une erreur lors de notre analyse : L'association Regrouper est en fait une entité LigneCommande. Elle devrait donc contenir un identifiant. En effet, il va nous falloir savoir gérer chaque ligne de commande, comme si elle était sur une fiche type, ce qui reprend notre concept de base de l'entité.
Maintenant, nous avons donc deux entités que nous allons essayer d'associer : Messager et LigneCommande.
Utilisons donc la petite phrase magique :
« Est-ce que un {Messager} peut {Livrer} plusieurs {LignesCommandes} » => Oui
« Est-ce que une {LignesCommandes} peut {être livrée par} plusieurs {Messagers} » => Oui (ou par le même messager, mais en plusieurs fois, ce qui revient globalement au même)
Cette fois, notre association est une association n-n (plusieurs-à-plusieurs). Nous devons donc :
Ces quelques réflexions nous amènent à un tout autre modèle, que voici :
Et, jusque là, nous n'avons parlé que de l'entité Messager !
Vous comprenez bien qu'il est particulièrement important de prendre son temps, et de bien réfléchir pour créer un modèle qui corresponde vraiment, le plus précisément possible, au système que vous essayez de décrire.
Vous êtes toujours avec moi ? Ca va ? Ce n'est pas trop dur ?
Alors, continuons, et intéressons-nous maintenant uniquement à ce qui se passe autour de l'entité Employe.
L'intitulé de l'exercice précise : "il s'agit de l'employé ayant pris la commande". Il y a donc une association apparente entre ces deux entités. Posons la fameuse question magique :
« Est-ce que un {Employe} peut {Prendre} plusieurs {Commandes}» => Oui
« Est-ce que une {Commande} peut {être Prise} par plusieurs {Employes} » => Non
On en déduit une association de 1-n (un-à-plusieurs), et nous savons que, dans ce cas, il suffit de reproduire l'identifiant du côté 1 (Employe) dans l'entité côté plusieurs (Commande), ce qui donnera ce résultat-ci :
Nous allons nous arrêter ici, mais, encore une fois, il aurait fallu prendre le temps de la réflexion. Par exemple, dans le modèle présenté ci-dessus, vous ne pouvez pas suivre les altérations que subit une commande - et il et tout à fait probable que vous n'en ayez aucun intérêt. Mais imaginez un peu qu'il vous faille savoir
Rien, dans le modèle actuel ne vous permet cela !
Vous constatez encore une fois qu'il ne faut pas se précipiter dans votre modélisation, afin de ne pas vous fourvoyer. Prenez le temps de poser des questions, et de bien réfléchir à ce qui se passe dans la réalité dans le système que vous modélisez. Prévoyez, prévoyez, prévoyez, mais sans exagérer.
Ce n'est pas le moment de se décourager ! On n'a pas fini
Intéressons-nous maintenant à l'entité Fournisseur. Il s'agit du fournisseur auquel je peux m'approvisionner en Produit.
Donc il existe une association entre Produit et Fournisseur, l'association Fournir, car il est vrai qu'un fournisseur fait rarement autre chose que de fournir
« Est-ce que un {Fournisseur} peut {Fournir} plusieurs {Produits} » => Oui
« Est-ce que un {Produit} peut {être Fourni} par plusieurs {Fournisseurs} » => Oui
ATTENTION ! Etes-vous vraiment sûrs de cette dernière affirmation ? Imaginez que vous soyez une grande surface, pensez-vous vraiment que vous allez vous fournir le même produit (par exemple les Yaourts Tartempion) chez plusieurs fournisseurs ? Non ! Vous allez vous fournir directement auprès de l'entreprise Tartempion. Donc, si, dans votre cas, vous vous fournissez directement à la source, il est peut vraisemblable que la deuxième réponse soit Oui. Si le fournisseur ne fabrique plus son produit, vous aurez beau vous retourner n'importe où, CE produit-là n'existera plus. Ceci est encore une fois un exemple de l'importance de prendre son temps et de bien réfléchir sur les implications des décisions que nous prenons dans l'élaboration de notre modèle.
Si, comme nous l'avons défini ci-dessus, nous sommes en présence d'une relation de n-n (plusieurs-à-plusieurs), alors, le modèle donnerait quelque chose comme ceci :
Notez bien que nous avons profité de l'occasion pour stocker une information dans l'association Fournir : Le coût du produit, lorsque je me fournis chez un Fournisseur donné.
Sinon, c'est à dire, si l'association était une association 1-n (un-à-plusieurs), le modèle se retrouverait assez changé, puisque nous obtiendrions ce genre de modèle :
Modèle pour lequel nous nous fournissons directement chez le producteur ou le fabricant.
Vous avez constaté par vous-même qu'il y avait une multitude de possibilités pour répondre à cet exercice.
Cependant, comme il va nous resservir ultérieurement, je prends volontairement le parti de ne vous présenter ici que le résultat final dans sa forme la plus simple :
| Mes tutoriels : |
| Mes articles : |
| Mes sites : |
| En cours de production : |
| En Projet : |
| Listing explicatif des fonctions d'Access |
| Access - Perfectionnement : Exploitation des données |
| Access - Expert : Interface et automatisation |
| Access - VBA |
| Access - VBA : Perfectionnement |
