Informatique Bases de données TD ACCESS Travail Dirigé : "Facturation Fac...
- ACCESS 2007 – 2010 - Travail dirigé Facturation – Table de...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –CH7. FACTURATION: EXTENSIONS A FAIRE SEUL ........................
- ACCESS 2007 – 2010 - Travail dirigé Facturation –CH16. CORRECTION EXTENSION : FORMULAIRES...........................
- ACCESS 2007 – 2010 - Travail dirigé Facturation – Ch1. Base de données "Facturation" A partir d’un cahier des charge...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –Ch2. Les Tables de base Objectif d...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Créer la table Client Voir exempl...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Créer la table ProduitOnglet Créer, Bouton Création de ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation – Passer en mode Création par le bouton (en haut à gauche) de...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Deux régles de base pour créer des tablesSi on ne dispose pas dune a...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Etude des Commandes1) Relation Père – FilsUn Client fait ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Créer la table CommandeEnTeteUn Client fait référence à au plus p...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –a) La Commande 1 fait référence à un seul client (le 3) par le champ Re...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –D. Créer la table CommandeLigneProduitUne Commande fait référence à...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –Enregistrer la table : CommandeLigneProduit ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –E. Conventions de nommage dans les Tables- Pour les noms des champs :Ja...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –4. Sécurité de lutilisation de lapplicationPar défaut, Access est confi...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –5. Utiliser les tables en mode InteractifA. Rechercher des donnéesOuvri...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –6. Définir les relations entre les tablesNous avons maintenant 4 tables...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –3° lien :1 Produit peut avoir N CommandeLigneProduit et1 CommandeLigneP...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Importance des relations dans un SGBD RelationnelAccess est un SGBD ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Tester lIntégrité Référentielle des RelationsLIntégrité Référentiell...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –7. Résumé des Tables  4 tables avec : Nom des champs, Clé primaire, ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –Ch3. Les requêtes de baseLa requête permet de faire des TRAITEMENTS sur...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –2) Requête "R02 Client Et"Question : "Liste des clients nommés TERIEUR ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –5) Exercices à faire A FAIRE…1 : nomme...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Requête multitable simple avec Relation1) Requête "R09 Commande et s...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –2) Requête "R10 Client et ses Commandes" ...
- ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Requête multitable avec calcul par enregistrement1) Requête "R11 Pro...
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
Polycop td access 2007 facturation
of 123

Polycop td access 2007 facturation

Published on: Mar 4, 2016
Published in: Education      Technology      Economy & Finance      
Source: www.slideshare.net


Transcripts - Polycop td access 2007 facturation

  • 1. Informatique Bases de données TD ACCESS Travail Dirigé : "Facturation Facturation" Marc SOUQUES souques@ensat.fr Version du 14 avril 2011 Ce document appartient à : ____________________________ INSTITUT NATIONAL POLYTECHNIQUEECOLE NATIONALE SUPERIEURE AGRONOMIQUE DE TOULOUSE Av de l’Agrobiopole, BP 107, 31 326 Auzeville Tolosane Tel : 05.34.32.39.00
  • 2. - ACCESS 2007 – 2010 - Travail dirigé Facturation – Table des MatièresCH1. BASE DE DONNEES "FACTURATION".............................................................................................................5 1. C AHIER DES CHARGES POUR APPLICATION "FACTURATION" ........................................................................................5 2. MODELE P HYSIQUE AVEC SES LIENS SUR LE SGBD A CCESS ........................................................................................5CH2. LES TABLES DE BASE ..........................................................................................................................................6 1. C ONCEPTION ET C ONSTRUCTION DE LA BASE DE DONNEES ......................................................................................... 6 2. C REER LES TABLES S IMPLES ........................................................................................................................................6 A. Créer la table Client ..............................................................................................................................................7 B. Créer la table Produit............................................................................................................................................8 3. C REER LES TABLES C OMPLEXES ..................................................................................................................................9 A. Deux régles de base pour créer des tables .......................................................................................................... 10 B. Etude des Commandes......................................................................................................................................... 11 C. Créer la table CommandeEnTete ........................................................................................................................ 12 D. Créer la table CommandeLigneProduit .............................................................................................................. 14 E. Conventions de nommage dans les Tables .......................................................................................................... 16 F. Importer des données dExcel dans Access.......................................................................................................... 16 G. Exporter des données dAccess vers Excel .......................................................................................................... 16 4. S ECURITE DE LUTILISATION DE LAPPLICATION ......................................................................................................... 17 5. U TILISER LES TABLES EN MODE I NTERACTIF.............................................................................................................. 18 A. Rechercher des données ...................................................................................................................................... 18 B. Remplacer des données ....................................................................................................................................... 18 C. Tester les boutons… ............................................................................................................................................ 18 6. D EFINIR LES RELATIONS ENTRE LES TABLES .............................................................................................................. 19 A. Importance des relations dans un SGBD Relationnel ......................................................................................... 21 B. Des régles dans les relations ............................................................................................................................... 21 C. Tester lIntégrité Référentielle des Relations ....................................................................................................... 22 7. R ESUME DES T ABLES ................................................................................................................................................. 23CH3. LES REQUETES DE BASE................................................................................................................................... 24 1. R EQUETE SELECTION ................................................................................................................................................. 24 A. Requête simple sur une seule table ...................................................................................................................... 24 B. Requête multitable simple avec Relation ............................................................................................................. 27 C. Requête multitable avec calcul par enregistrement............................................................................................. 29 D. Erreur à éviter dans les requêtes multitable. ....................................................................................................... 31 E. Les fonctions du langage VBA pour les Requêtes................................................................................................ 32 F. Exercices à faire .................................................................................................................................................. 35 G. Requête avec calcul sur "Regroupement" denregistrements .............................................................................. 36 H. Exercices à faire .................................................................................................................................................. 36 2. R ESUME DES R EQUETES ............................................................................................................................................. 37CH4. LES FORMULAIRES DE BASE .......................................................................................................................... 38 1. F ORMULAIRE U NITAIRE BASE SUR UNE TABLE ........................................................................................................... 38 A. Créer un Formulaire Colonne simple.................................................................................................................. 38 B. Utiliser un formulaire .......................................................................................................................................... 38 C. Modifier un formulaire ........................................................................................................................................ 39 2. F ORMULAIRE T ABULAIRE BASE SUR UNE REQUETE.................................................................................................... 43 A. Créer la requête source du futur Formulaire ...................................................................................................... 43 B. Créer un formulaire Tabulaire basé sur la requête............................................................................................. 44 C. Ajouter des contrôles ........................................................................................................................................... 44 D. Exercices à faire .................................................................................................................................................. 45 3. R ESUME DES F ORMULAIRES....................................................................................................................................... 45CH5. LES TABLES AVEC LISTES DEROULANTES DE CHOIX POUR CLE ETRANGERE........................... 46 1. T ABLES ACTUELLES BASIQUES................................................................................................................................... 46 2. C HAMP C LE ETRANGERE AVEC LISTE DEROULANTE DE C HOIX .................................................................................. 46 A. Modifier propriétés Liste de choix des champs clés étrangères .......................................................................... 47 B. Exercice à faire… ................................................................................................................................................ 49CH6. BILAN DE LAPPLICATION ............................................................................................................................... 51 1. D ES TABLES ............................................................................................................................................................... 51 2. D ES R EQUETES .......................................................................................................................................................... 51 3. D ES F ORMULAIRES .................................................................................................................................................... 51 Ensat - Informatique Appliquée - Marc Souques Page 2
  • 3. - ACCESS 2007 – 2010 - Travail dirigé Facturation –CH7. FACTURATION: EXTENSIONS A FAIRE SEUL ............................................................................................ 52 1. C AHIER DES CHARGES POUR LES TABLES ................................................................................................................... 52 A. Table simple......................................................................................................................................................... 52 B. Table complexe.................................................................................................................................................... 52 2. C AHIER DES CHARGES POUR LES REQUETES .............................................................................................................. 53 A. Requête simple ..................................................................................................................................................... 53 B. Requête multitable ............................................................................................................................................... 53 3. C AHIER DES CHARGES POUR LES FORMULAIRES ........................................................................................................ 55 A. Formulaire simple ............................................................................................................................................... 55 B. Formulaire Tabulaire basé sur une requête ........................................................................................................ 55 C. Formulaire "Produit" à compléter ...................................................................................................................... 55CH8. LETAT .................................................................................................................................................................... 56 1. C REER UN ETAT ......................................................................................................................................................... 56 A. Etat basé sur une table ........................................................................................................................................ 56 B. Etat basé sur une requête .................................................................................................................................... 57 2. R ESUME DES E TATS ................................................................................................................................................... 60CH9. LES FORMULAIRES COMPLEXES .................................................................................................................. 61 1. F ORMULAIRE AVEC S OUS-FORMULAIRE ..................................................................................................................... 61 2. F ORMULAIRE AVEC S OUS FORMULAIRE COMPLEXE ................................................................................................... 63 A. Version basée sur 2 Tables (pour FP) et 2 Tables (pour SF). ............................................................................. 63 B. Version basée sur 3 niveaux de Tables................................................................................................................ 64 C. Version avec 2 sous formulaires de même niveau. .............................................................................................. 64 3. F ORMULAIRE AVEC GRAPHIQUE ................................................................................................................................. 67 A. Graphique simple basé sur une Table ................................................................................................................. 67 4. F ORMULAIRE MENU PRINCIPAL ................................................................................................................................. 69 5. R ESUME SUR LES F ORMULAIRES ................................................................................................................................ 70CH10. LES MACROS ........................................................................................................................................................ 72 1. MACRO DE DEMARRAGE AUTOMATIQUE : AUTOEXEC ............................................................................................... 72 2. MACROS POUR OUVRIR FORMULAIRES ET ETATS........................................................................................................ 72 A. Créer la macro .................................................................................................................................................... 72 B. Attacher une macro à un événement d’un contrôle Bouton................................................................................. 73CH11. LES RECHERCHES .............................................................................................................................................. 74 1. R ECHERCHE MONO CRITERE, MONO VALEUR DU CRITERE, MONO RESULTAT ............................................................. 75 A. Rechercher un client sur son Nom Prénom par liste déroulante......................................................................... 75 B. Auto-Actualiser la liste déroulante sur sa définition ........................................................................................... 77 2. R ECHERCHE MONO CRITERE, MONO VALEUR DU CRITERE, MULTI RESULTATS ........................................................... 77 A. Rechercher des clients sur leur ville par liste déroulante ................................................................................... 77 3. R ECHERCHE MONO CRITERE, MULTI VALEURS DU CRITERE, MULTI RESULTATS ......................................................... 80 4. R ECHERCHE MULTI CRITERES, MONO VALEUR DU CRITERE , MULTI RESULTATS ......................................................... 80CH12. LES FORMULAIRES COMPLEXES SUITE ..................................................................................................... 82 1. S OUS FORMULAIRE BASE SUR R EQUETE AVEC CALCULS ............................................................................................ 82CH13. INTRODUCTION AUX BASES DE DONNEES ET A ACCESS ................................................................... 83 1. U N S YSTEME DE GESTION DE B ASES DE DONNEES .................................................................................................... 83 2. U NE APPLICATION DE B ASE DE D ONNEES.................................................................................................................. 83 3. U NE B ASE DE DONNEES ............................................................................................................................................. 83 4. ACCESS ................................................................................................................................................................... 84 A. Choix entre Access et Excel pour développer une application ? ......................................................................... 84CH14. CORRECTION EXTENSION: LES TABLES .................................................................................................... 85 A. Créer la table Categorie ...................................................................................................................................... 85 B. Modifier la table Produit ..................................................................................................................................... 85 C. Créer la table Fournisseur .................................................................................................................................. 86 D. Créer la table Intermédiaire ProdFour ............................................................................................................... 86 E. Tirer les Relations avec Intégrité Référentielle: .................................................................................................. 88 F. Modèle Conceptuel de Données (MCD) à la source du Modèle Physique Access............................................. 88CH15. CORRECTION EXTENSION: LES REQUETES .............................................................................................. 89 A. Requête simple ..................................................................................................................................................... 89 B. Requête multitable ............................................................................................................................................... 89 Ensat - Informatique Appliquée - Marc Souques Page 3
  • 4. - ACCESS 2007 – 2010 - Travail dirigé Facturation –CH16. CORRECTION EXTENSION : FORMULAIRES.............................................................................................. 91 A. Formulaire simple ............................................................................................................................................... 91 B. Formulaire Tabulaire basé sur une requête ........................................................................................................ 91 C. Formulaire produit à compléter .......................................................................................................................... 91CH17. DES ERREURS A NE PAS FAIRE....................................................................................................................... 92CH18. GESTION DES IMAGES DEPENDANTES DENREGISTREMENTS DUNE TABLE ............................... 93 1. I MAGES ET T ABLE : VERSION1 BASEE SUR C HAMP OLE ............................................................................................ 93 A. Gérer un champ OLE .......................................................................................................................................... 93 B. Gérer un contrôle de formulaire pour afficher limage ....................................................................................... 93 2. I MAGES ET T ABLE : VERSION 2 BASEE SUR C HAMP TEXTE ET MACRO DAFFECTATION ............................................. 94 3. I MAGES ET T ABLE : VERSION 3 BASEE SUR C HAMP TEXTE ET P ROCEDURE-VBA DAFFECTATION ............................ 97 4. R ESUME :S AISIR, STOCKER, MODIFIER ET S UPPRIMER DES IMAGES DANS DES ENREGISTREMENTS DE TABLE . .......... 99CH19. LES TABLES AVEC CHAMPS A LONGUEUR VARIABLE ........................................................................ 100 1. C HAMP DE TYPE MEMO............................................................................................................................................ 100 2. C HAMP DE TYPE P IECE JOINTE ................................................................................................................................. 100 3. C HAMP AVEC PLUSIEURS VALEURS .......................................................................................................................... 101 A. Démarche Classique Relationnelle.................................................................................................................... 102 4. F ORMULAIRE BASE SUR UN MODELE DE FORMULAIRE.............................................................................................. 105 A. Créer un modèle de formulaire.......................................................................................................................... 105 B. Créer un formulaire basé sur un modèle de formulaire................................................................................... 105CH20. LES MODES DUTILISATION DE LAPPLICATION ................................................................................... 106 1. MODE UTILISATEUR : CONFIGURATION DU DEMARRAGE ......................................................................................... 106 2. MODE PARTAGE DE DONNEES ENTRE PLUSIEURS UTILIS ATEURS EN RESEAU LOCAL ................................................ 107CH21. LES MACROS AVANCEES................................................................................................................................ 108 1. T ESTER LES EVENEMENTS DECLANCHANT DES MACROS DANS UN FORMULAIRE ...................................................... 108 2. MACRO CONDITIONNELLE ATTACHEE A UN EVENEMENT ......................................................................................... 109 3. R EFERENCE DES ACTIONS DES MACROS .................................................................................................................. 111CH22. MODIFICATION DES DONNEES DES TABLES ........................................................................................... 113 1. MODIFICATION MANUELLE ...................................................................................................................................... 113 2. MODIFICATION AUTOMATIQUE PAR REQUETE ......................................................................................................... 114 A. La requête action ............................................................................................................................................... 114 B. La requête action paramétrée par formulaire ................................................................................................... 115 3. MODIFICATION AUTOMATIQUE PAR MACRO ............................................................................................................ 117 A. Mise à jour Automatique Invisible du stock de Produit sur événement Saisie de la Quantité commandée ....... 117CH23. TRAITEMENTS DE SYNTHESE ...................................................................................................................... 120 1. F ORMULAIRE T ABLEAU C ROISE DYNAMIQUE (TCD) .............................................................................................. 120 A. Créer la requête source du futur formulaire ..................................................................................................... 120 B. Créer le formulaire TCD basé sur la requête.................................................................................................... 121CH24. LA BASE DE DONNEES FINALE..................................................................................................................... 123 Ensat - Informatique Appliquée - Marc Souques Page 4
  • 5. - ACCESS 2007 – 2010 - Travail dirigé Facturation – Ch1. Base de données "Facturation" A partir d’un cahier des charges définissant le sujet de lapplication, création de la base de données. 1. Cahier des charges pour application "Facturation" Gérer une application commerciale de facturation pour PME. Il existe des ensembles de Clients, de Commandes et de Produits. Un Client fait référence à au plus plusieurs () Commandes. Une Commande fait référence à au plus 1 seul Client. Une Commande fait référence à au plus plusieurs Produits (commandés). Un Produit fait référence à au plus plusieurs Commandes (où il est commandé) Pour 1 Commande ET pour 1 Produit, il y a 1 Quantité de produit commandée. 2. Modèle Physique avec ses liens sur le SGBD Access Structure de la base de données à créer. Ci-dessous, les champs des tables avec les VALEURS assurant les Relations.Clé primaire Clé primaireSANS doublon SANS doublon Clé primaire SANS doublon basée sur 2 champs Clé primaire Clé SANS doublon étrangère AVEC doublons Clé Clé étrangère étrangère AVEC AVEC Légende : doublons doublons Clé primaire mono ou multi champ Clé étrangère liée à une clé primaire Ensat - Informatique Appliquée - Marc Souques Page 5
  • 6. - ACCESS 2007 – 2010 - Travail dirigé Facturation –Ch2. Les Tables de base Objectif du TD :Construire une Base de Données de FACTURATION basée sur plusieurs Tables : - Client - Produit - Commande - Produit Commandé pour commencer1. Conception et Construction de la Base de donnéesLorsque lon veut construire une Maison, on doit procéder en 3 temps :a) Créer sur le papier un Plan Conceptuel sans aspect technique des différentes Pièces qui composentla future Maison et gérer les Liens et Interdépendances qui existent entre ces Pièces.b) Créer sur le papier un Plan Logique, à partir du Plan Conceptuel, en y ajoutant les élémentstechniques (type de matériaux, épaisseur isolation, câblage électrique, réseau eau, chauffage, …).c) Construire sur le Terrain la Maison Physique , à partir du Plan Logique.En informatique, pour construire une Base de Données, on procède de même en 3 temps :a) Créer sur le papier un Modèle Conceptuel (sans aspect technique) des différentes Entités quicomposent la future Base de Données et gérer les Associations (liens) qui existent entre ces Entités.b) Créer sur le papier un Modèle Logique, à partir du Modèle Conceptuel, en y ajoutant les élémentstechniques (Clé primaire, Clé étrangère, Relations dune Base de Données RELATIONNELLE)c) Construire sur lOrdinateur (Access) la Base de Données Physique , à partir du Modèle Logique. (Voir éventuellement Cours de Modélisation présentant ces 3 modèles)Ce TD vise à utiliser ACCESS pour créer directement sur ordinateur une Base de Données Physiquemais il est recommandé de se documenter sur les cours de Modélisation des Données quipermettent de créer Modèles Conceptuels de Données (MCD) et Modèles Logiques de Données (MLD).Vous trouverez plus loin dans ce document (fin Chapitre 14) le MCD (Modèle Conceptuel des Données)correspondant à la base de données Access développée dans ce TD.Notre démarche consiste, pour la partie concernant les Données, à établir au fur et à mesure quelquesrègles simples pour définir les Tables de notre application de Facturation.Démarrer ACCESS .Clic sur Base de donnée vide, Clic sur pour choisir un dossier puis nommer lapplication ESSAI(lextension accdb signifie Access Data Base cest le format 2007 et 2010) et bouton Créer.- Si besoin, compréhension et configuration de lenvironnement de travail :Bouton Office (Fichier en Version 2010) puis Options Access : - Standard : Définir le dossier de données par défaut pour ouvrir ou enregistrer des bases - Base de données active : Cocher Compacter lors de la fermeture (pour réduire la taille du fichier accdb) - Personnaliser : Définir les icones de la barre daccès rapideen haut décran et valider OK.2. Créer les Tables SimplesNous allons créer les tables Client et Produit qui sont simples car chaque enregistrement ne fait pasréférence à au plus 1 seul enregistrement dune autre table.Un Client ne fait pas référence à au plus 1 seule Commande ou à au plus 1 seul Produit commandé.Un Produit ne fait pas référence à au plus 1 seule Commande ou à au plus 1 seul Client.Fermer la table ouverte par défaut.Onglet Créer, Bouton Création de Table pour être en mode Création Ensat - Informatique Appliquée - Marc Souques Page 6
  • 7. - ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Créer la table Client Voir exemple en bas de pageNom du Champ Type de données Propriétés Un nombre qui s’incrémenteNum Client NuméroAuto automatiquement à chaque nouveauNom Texte Taille : 25 client saisi : 1 puis 2 puis 3 (Attention : le type Format : > (conversion en Majuscule) Texte est limité à Null interdit : OUI (saisie obligatoire) (Null = VIDE) 255 caractères !) Chaine vide autorisée : NON Si besoin de plus de 255Prénom Texte Taille : 15 Null interdit : OUI caractères, utiliser le Chaine vide autorisée : NON type Mémo (taille illimitée)Adresse Texte Taille : 30 Fonction Date() retournant laCode Postal Texte (pas Num car on Taille : 5 date du jour. On peut la ne fait pas de calcul sur Null interdit : NON donc trouver par , Fonction, les codes postaux !) saisie facultative Fonction intégrées,Ville Texte Taille : 25, Format : > (Majuscule) Date/heure, Date Valeur par défaut : TOULOUSEDate Entrée Date/Heure Format : Date, abrégé Valeur par défaut : Date( ) Afficher le sélecteur : À certaines datesUne clé primaire est un (ou plusieurs) champ de la table qui identifie sans ambiguïté chaqueenregistrement de la table (chaque élément de la population ). Deux mêmes valeurs dans 2enregistrements de ce champ sont interdites (pas de doublons: 2 personnes ne doivent pas avoir le mêmenuméro de client 3).Définir une clé primaire sur le champ: Num Client en sélectionnant ce champ puis barre d’outilsClé primaire (Cela bascule automatiquement la propriété Index du champ Num Client : Oui SANS doublon).Convention personnelle pour Nom de la Clé primaire : Num + (Nom de table) = Num ClientOn souhaite que le couple Nom Prénom soit unique, interdire 2 fois LECLERC Alain. Clic sur Index :Donner librement un Nom à un Index : NomPrenomChoisir dans la liste déroulante les 2 champs à indexer : Nom puis PrénomSélectionner la ligne NomPrenom et saisir Unique : Ouipour éviter les doublons sur ce couple de champsEnregistrer la table (disquette de la barre outils Accès rapide) : ClientPasser en mode Feuille de données pour saisir 3 Clients comme ci-dessus :Ok pour message erreur ci-dessus, pour éviter le doublon MUILLARD Marc, modifier lenregistrement3 en DURAND Alain pour avoir :Passer en mode Création pour consulter la structure de la table puis fermer (croix à droite) la table Client. Ensat - Informatique Appliquée - Marc Souques Page 7
  • 8. - ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Créer la table ProduitOnglet Créer, Bouton Création de Table pour être en mode CréationNom du Champ Type de données Propriété du champ Si besoin de plus de 255 caractères,Num Produit Texte Taille : 6 utiliser le type Mémo (taille illimitée)Désignation Texte Taille : 60Prix Ht Monétaire Valide si : >=3 (pour afficher € uros) Message si erreur :Vérifier le minimum (>=3 € )Taux Tva Numérique Taille : Réel simple (Touche F1 pour l’Aide) Ici, le Num Produit nest pas en NuméroAuto Format : Pourcentage (%) car on veut gérer notre propre Numérotation : Valeur par défaut : taper 0.196 pour obtenir 3 lettres pour la marque du produit et 0,196 (virgule à la place du point) 3 chiffres pour le rang du produit dans la marque Valide si : 0.196 Ou 0.055 (ACE002 pour le 2° produit de marque ACER) Message si erreur : Vérifier le taux Le Prix Ttc est une valeur calculée (Prix Ttc = Prix Ht * (1+Taux Tva)). On ne stocke pas dans une Table un champ calculé car on risque lerreur davoir calculé et stocké un PrixTtc sur un ancien Prix Ht : exemple Prix Ht = 100 ; Prix Ttc = 119.6 puis modif Prix Ht = 95 et Prix Ttc = 119.6 faux car non modifié On calculera donc le Prix Ttc (sur les actuels Prix Ht et Taux Tva) chaque fois que lon en aura besoin même si cela prend un peu (si peu) de temps-machine. Règle : La Table stocke des informations de base (non calculées)Une clé primaire est un (ou plusieurs) champ de la table qui identifie sans ambiguïté chaqueenregistrement de la table (chaque élément de la population). Deux mêmes valeurs dans deuxenregistrements de ce champ sont interdites (pas de doublons: 2 produits ne doivent pas avoir le mêmenuméro de produit).Définir une clé primaire sur le champ: Num Produit en sélectionnant ce champ puis barre d’outilsClé primaire (Cela bascule automatiquement la propriété Index du champ Num Produit : Oui SANS doublon).Convention personnelle pour Clé primaire : Num + (Nom de table) = Num ProduitOn souhaite que la Désignation du produit soit unique, interdire 2 fois Imprimante laser Canon type 1.On veut donc un index sans doublon (unique) sur le seul champ DésignationCompléter la description du champ Désignation avec :Désignation Texte Indexé : Oui sans doublonsIci, il est inutile de passer par Clic sur Index car lindex estici MONO-Champ.Par contre, le résultat est bien stocké dans les index :ATTENTION :Num Produit Texte Indexé : Oui sans doublons car Clé PrimaireDésignation Texte Indexé : Oui sans doublons sans être Clé primaireLa clé primaire est le champ qui identifie chaque enregistrement de la table, cest cette information quiest utilisée dans une autre table pour pointer (référencer) vers la table Produit.Enregistrer la table : Produit puis Fermer la table :Remarque : Les noms des tables sont notés au singulier (Client, Produit) sans préfixe Tab ProduitsFaire un effort dans le choix des noms des tables pour que le nom corresponde au contenu de la table : Les produits Catalogue produit Produits vendus Visualisation en mode feuille de donnéesOuvrir la table Produit par double-clic sur Produit : Ensat - Informatique Appliquée - Marc Souques Page 8
  • 9. - ACCESS 2007 – 2010 - Travail dirigé Facturation – Passer en mode Création par le bouton (en haut à gauche) de longlet Accueil : Passer en mode Feuille de données par le même bouton de barre d outils : (en haut à gauche) Saisir : (remarque les erreurs volontaires sont en italique et souligné ci-dessous) Num Produit Désignation Prix Ht Taux Tva Erreur CAN001 Imprimante laser 1 puis 300 19.6% Erreur sur le Prix Canon type 1 MIC001 Initiation à 15 20.6% puis 5.5% Erreur sur la Tva ACCESS pour erreur La tva sur les livres est à 5.5%erreur les Nuls erreur CAN001 Imprimante 1000 19.6% Erreur sur Num Produit détectée seulement après saisie de Taux Tva (doublon dans clé primaire avec le 1° enregistrement) : erreur modifier ce Num Produit en CAN002 EPS001 Imprimante 75 19.6% Erreur sur Désignation (doublon sur propriété "OUI sans doublon"), modifier cette Désignation avec Imprimante Jet encre Epson série1 Remarque sur le champ Num Produit : les 3 premières lettres correspondent à la marque du produit. CAN pour Canon, EPS pour Epson, MIC pour léditeur Microapplication Il y a vérification automatique de l’unicité (Unique) de la clé Primaire de Num produit. Remarque : les Données saisies sont enregistrées automatiquement (pas besoin denregistrer sauf la mise en forme ou les définitions des champs des tables). Elargir la colonne Désignation (modification de la mise en forme). Fermer la table : (lenregistrement de la mise en forme de la table est proposé) Ouvrir la table : elle est triée sur la clé primaire : Num Produit Ajouter un enregistrement par ce bouton dans la dernière ligne de la table signalée par (ce symbole sur une ligne vierge indique la position pour ajouter des enregistrements) et tester l’erreur sur le taux de TVA: CAN003 Imprimante Canon couleur 300 33.33% puis 19.6% Supprimer lenregistrement ci-dessus en le sélectionnant préalablement par la colonne grisée à gauche de Num Produit puis Suppr du clavier. Fermer la table : 3. Créer les Tables Complexes Nous allons créer les tables pour gérer les Commandes de Produits faites par les Clients. Il y a 2 aspects complexes concernant les Commandes de Produits : a) Chaque enregistrement dune table1 fait référence à au plus 1 seul enregistrement dune table2. - Une Commande fait référence à au plus 1 seul Client. b) Chaque enregistrement dune table1 fait référence à au plus N enregistrements dune table2 et chaque enregistrement dune table2 fait référence à au plus N enregistrements dune table1. - Une Commande fait référence à au plus N Produits qui sont commandés et un Produit fait référence à au plus N Commandes où il est commandé. Ensat - Informatique Appliquée - Marc Souques Page 9
  • 10. - ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Deux régles de base pour créer des tablesSi on ne dispose pas dune analyse des données sous forme de Modèle Conceptuel de Données (MCD)et Modèle Logique de Données (MLD) , on peut utiliser les règles suivantes directement dans Access.De manière très simplifiée et limitée , il existe 2 types principaux de relation entre 2 tables : Relation Père - Fils et Relation Frère - Sœur1) Relation Père - Fils : PERE FILSUn Père peut avoir de 0 à N Fils, un Fils peut avoir de 0 à 1 Père. x xPour gérer cette relation, on ajoute dans la table Fils x xun champ Ref Père contenant la valeur de lidentification du Père. x x Clé étrangère Clé primaire x AVEC doublons SANS doublon Père sans fils ou ayant perdu son ou ses fils Fils de père Table Fils Table Père inconnuConvention perso pour nom Clé étrangère : Ref + (Nom de la table liée) = Ref Père (en italique bleu)Convention perso pour nom Clé primaire : Num + (Nom de la table) = Num Fils (en gras rouge) Demander à voir éventuellement exemple diapositive : "PV et sa Voiture".2) Relation Frère - Sœ urUn Frère peut avoir de 0 à N Sœ une Sœ peut avoir de 0 à N Frères. urs, ur FRERE SOEURPour gérer cette relation, on ajoute une nouvelle table intermédiaire avec x x un champ Ref Frère un champ Ref Soeur Déventuels champs x x contenant la valeur de contenant la valeur de dépendants de frère x x lidentification de Frère lidentification de Sœ ur ET sœ ur. x x Clé étrangère Clé étrangère Clé primaire AVEC doublons AVEC doublons Clé primaire SANS doublon SANS doublon Table Frère Table Croisement : Frère - Sœur Table SœurAutre présentation non informatique de la table Croisement Frère - SœurA laide de ces 2 règles de base, nous allons gérer les Commandes de Produits faites par les Clients. Ensat - Informatique Appliquée - Marc Souques Page 10
  • 11. - ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Etude des Commandes1) Relation Père – FilsUn Client fait référence à au plus N Commandes.Une Commande fait référence à au plus 1 seul Client. Convention : gras + souligné Table Commande indique une Table Client clé primaire2) Relation Frère – SœurUne Commande fait référence à au plus N Produits qui sont commandés.Un Produit fait référence à au plus N Commandes qui le commandent. Table Commande Table Produit Table Croisement : Commande - ProduitOn nomme généralement cette table de Croisement en mixant les noms des 2 tables croisées, parexemple : ComProd ou ProdCom Ensat - Informatique Appliquée - Marc Souques Page 11
  • 12. - ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Créer la table CommandeEnTeteUn Client fait référence à au plus plusieurs () Commandes.Une Commande fait référence à au plus 1 seul Client.Nous sommes donc dans la règle Père – Fils vue précédemment.Un Père fait référence à au plus plusieurs FilsUn Fils fait référence à au plus 1 seul PèreDonc nous gérerons un champ Ref Client dans la table Commande pour pointer vers le champNum Client Clé primaire de la table Client.Ce champ Ref Client est appelé Clé étrangère car il sera relié plus tard à Num Client.On gère ici la partie fixe de la Commande, le haut de la commande, son En-tête, sans prendre encompte les produits commandés car ils sont en nombre variable. Un nombre qui s’incrémenteNom du Champ Type de données Propriétés automatiquement à chaque nouvelleNum Commande NuméroAuto commande saisie : 1 puis 2 puis 3Ref Client Numérique Taille : Entier long ; PAS de valeur par défaut(pour futur lien vers Num Null interdit : OUI donc saisie obligatoire d1 valeur deClient Chaine vide autorisée : NONde table CLIENT) (pour future compatibilité avec Num Client de la table Client qui est NuméroAuto donc en Numérique-Entier long)Date Commande Date/Heure Format : Date, abrégé (jj/mm/aaaa) Valeur par défaut : Date( ) (=date du jour) Afficher le sélecteur : À certaines datesDate Echéance Date/Heure Format : Date, abrégé Aujourdhui Valeur par défaut : Date( ) + 30 + 30 jours Afficher le sélecteur : À certaines datesPayée Oui/Non Valeur par défaut : NonTotal Commande : NE PAS gérer car ce nest pas une DONNEE mais un CALCUL Remarque : Dans cette table CommandeEnTete, le nom Ref Client est choisi librement. Je prends la règle Ref+NomTable liée pour la Clé étrangère mais dautres solutions sont possibles. (Num Client est souvent utilisé mais confusion possible avec le nom et le rôle de Num Client qui est clé primaire de la table CLIENT)Définir la clé primaire sur le champ : Num CommandeEnregistrer la table : CommandeEnTete Donnée existant dans la clé primaire Num Client de laPasser en mode Feuille de données : table ClientSaisir : Table ClientNum Commande Ref Client Date Commande Date Echéance Payée 1 (Automatique) 3 (saisir une date) (saisir une date) Cocher pour Oui 2 (Automatique) 3 (valider date) (valider date) Rien pour Non 3 (Automatique) 1 (valider date) (saisir une date) Cocher pour OuiIl y a 3 CommandeEnTete. Ensat - Informatique Appliquée - Marc Souques Page 12
  • 13. - ACCESS 2007 – 2010 - Travail dirigé Facturation –a) La Commande 1 fait référence à un seul client (le 3) par le champ Ref Client.Num Commande Ref Client Date Date Echéance Payée Commande 1 3b) Le Client 3 fait référence à plusieurs Commandes (la 1 et la 2) par le champ Ref ClientNum Commande Ref Client Date Date Echéance Payée Commande 1 3 2 3Cest par cette technique que lon respecte le cahier des charges :Une Commande fait référence à au plus 1 seul Client.Un Client fait référence à au plus plusieurs () Commandes.Fermer la table CommandeEnTete.Le texte ci-dessous est une explication, il ny a rien à faire mais tout à lire. Gestion de la saisie dans la clé étrangère Ref Client :Pour le champ Ref Client, pour le moment, la saisie est faite au clavier en tapant manuellement 3 parexemple.En réalité, pour une liste de clients comme celle ci-contre,on ne peut faire référence pour Ref Client de la table CommandeEnTete quà 1des 3 clients identifiés (1, 2, 3) dans la clé primaire Num Client de la tableClient.Une erreur consisterait pour Ref Client à saisir un numéro 4 qui nexiste pas dans Num Client.Pour éviter cette erreur, nous gérerons plus tard (voir Chapitre 5 LES TABLES AVEC LISTESDEROULANTES POUR CLE ETRANGERE) la saisie dans Ref Client par une liste déroulante neproposant QUE les Clients qui existent dans la clé primaire Num Client de la table Client.Pour faciliter le choix du client de la commande, nous présenterons en plus de Num Client, le Nom et lePrénom du client en classant ces clients par ordre alphabétique des Noms – Prénoms.Nous pourrons même simplifier la saisie en naffichant pas le Numéro du Client.Le champ Ref Client a le rôle technique de gérer le lien entre les 2tables. Il a ici un nom peu compréhensible pour lutilisateur.La propriété Légende des champs permet de gérer un nom desubstitution qui saffichera à la place de Ref Client.Nous nutilisons pas cette procédure pour le moment pour ne pas compliquer la compréhension.Si vous relisez ce polycopié pour faire un projet Access, il faut traiter la gestion de ces listesdéroulantes de clés étrangères dès la création des Tables . Ensat - Informatique Appliquée - Marc Souques Page 13
  • 14. - ACCESS 2007 – 2010 - Travail dirigé Facturation –D. Créer la table CommandeLigneProduitUne Commande fait référence à au plus plusieurs Produits (commandés).Un Produit fait référence à au plus plusieurs Commandes.(où il est commandé)Pour 1 Commande ET pour 1 Produit, il y a 1 Quantité de produit commandée.Nous sommes donc dans la règle Frère – Sœ vue précédemment. urUn Frère fait référence à au plus plusieurs Sœ ursUne Sœ ur fait référence à au plus plusieurs FrèresDonc, alors que les tables CommandeEnTête et Produit sont déjà créées, nous allons créer une nouvelletable intermédiaire entre ces 2 tables pour assurer le lien plusieurs à plusieurs (N / N).Donc nous gérerons dans cette table un champ Ref Commande pour pointer vers le champNum Commande Clé primaire de la table Commande.Ce champ Ref Commande est appelé Clé étrangère car il sera relié avec le champ Num Commande.Donc nous gérerons dans cette table un champ Ref Produit pour pointer vers le champNum Produit Clé primaire de la table Produit.Ce champ Ref Produit est appelé Clé étrangère car il sera relié avec le champ Num Produit.On gère ici la structure de la table pour un Produit commandé d une CommandeEnTete. Cest la table de croisement entre la table CommandeEnTete et la table Produit.Nom du Champ Type de Propriétés donnéesRef Commande Numérique Taille : Entier long ; PAS de valeur par défaut (pour lien vers (pour future compatibilité avec Num Commande de la table Num Commande CommandeEnTete qui est en NuméroAuto donc en de table CommandeEnTete) Numérique-Entier long), Null interdit : OUI ; Chaine vide autorisée : NONRef Produit Texte Taille : 6 ; PAS de valeur par défaut , (pour lien vers (pour future compatibilité avec Num Produit de la table Num Produit Produit qui est Texte de longueur 6) de table Produit) Null interdit : OUI ; Chaine vide autorisée : NONQuantité Numérique Taille : Réel simple (au cas où on vendrait 1 boite et(quantité commandée dun demie de 10 DVD = 15 DVD = 1,5 boite)produit dune commande) Valeur par défaut : 1Ici, la clé primaire nest pas mono-champ. Cest le couple 1 - CAN001 qui identifieun Produit commandé (CAN001) dans le cadre dune Commande (1).Définir une clé primaire : sélectionner les 2 premierschamps Ref Commande, Ref Produit par un glissé à lasouris (dans la colonne de gauche) et les définir par le boutonClé primaire comme clé primaire composée :Attention : Ref Commande et Ref Produit ne sont pas individuellement clé primairemais le couple Ref Commande + Ref Produit est clé primaire. Il ny a qu1 Clé primaire par Table. Ensat - Informatique Appliquée - Marc Souques Page 14
  • 15. - ACCESS 2007 – 2010 - Travail dirigé Facturation –Enregistrer la table : CommandeLigneProduit Donnée existant dans la clé(Ce nom intégre Commande et Produit : les 2 tables croisées) primaire Num Produit de laPasser en mode Feuille de données : table ProduitSaisir :Ref Commande Ref Produit Quantité 1 CAN001 4 1 EPS001 2 Table Produit 2 CAN001 1 2 CAN002 3 2 MIC001 5 Table CommandeLigneProduitNous avons donc 5 Produits commandées dans CommandeLigneProduit répartis sur 2 Commandes.La Commande 1 (Ref Commande) contient 2 Produits, CAN001 et EPS001La Commande 2 (Ref Commande) contient 3 Produits, CAN001, CAN002 et MIC001Le Produit CAN001 (Ref Produit) a été commandé 2 fois, dans les Commandes 1 et 2.Un produit commandé est identifié par la clé primaire composite Ref Commande - Ref Produit .Le couple 1 - CAN001 identifie la commande du produit CAN001 dans la commande 1.Il est impossible de ressaisir ce couple 1 - CAN001 sinon Doublon sur clé primaire.Fermer la table CommandeEnTeteFermer Access et donc la base essai (croix en haut à droite)Le texte ci-dessous est une explication, il ny a rien à faire mais tout à lire. Gestion de la saisie dans les clés étrangères Ref Commande et Ref Produit :Pour le champ Ref Produit, pour le moment, la saisie est faite au clavier en tapant manuellementCAN001 par exemple.En réalité, pour une liste de produit comme celle ci-contre,on ne peut faire référence pour Ref Produit de la table CommandeLigneProduitquà 1 des n Produits identifiés (ACE001, ACE002, CAN001, …) dans la cléprimaire Num Produit de la table Produit.Une erreur consisterait pour Ref Produit à saisir ABC001 qui nexiste pas dans Num Produit.Pour éviter cette erreur, nous gérerons plus tard (voir Chapitre 5 LES TABLES AVEC LISTESDEROULANTES POUR CLE ETRANGERE) la saisie dans Ref Produit par une liste déroulante neproposant QUE les Produits qui existent dans la clé primaire Num Produit de la table Produit.Pour faciliter le choix du produit commandé, nousprésenterons en plus de Num Produit, laDésignation du produit en classant ces désignationsde produits par ordre alphabétique. Nous pourrons même simplifier la saisie en naffichant pas le Numéro du Produit.Si vous relisez ce polycopié pour faire un projetAccess, il faut traiter la gestion de ces listesdéroulantes de clés étrangères dès la création desTables.Faire de même pour lautre clé étrangère Ref Commande de cette table. FIN TD1 Ensat - Informatique Appliquée - Marc Souques Page 15
  • 16. - ACCESS 2007 – 2010 - Travail dirigé Facturation –E. Conventions de nommage dans les Tables- Pour les noms des champs :Jai choisi dutiliser les possibilités dAccess avec espaces et accents car ces noms sont affichés àlutilisateur de lapplication.Je mets une majuscule à la première lettre de chacun des mots composant le Champ.Exemple : Date Echéance avec 2 majuscules, 1 espace entre Date et Echéance et 1 accent.Remarque : On peut aussi gérer des noms sans espace et sans accent (DateEcheance) pour faciliterlécriture de ces noms en programmation VBA et gérer dans ce cas la propriété du champ Légende quipropose un nom de substitution (avec espaces et accents : Date dEchéance) pour les affichages dans lesformulaires- Pour les noms des tables :Jai choisi de NE PAS utiliser les possibilités despace et daccent car ces noms NE SONT PAS affichésà lutilisateur de lapplication.Je mets une majuscule à la première lettre de chacun des mots composant la Table.Je note les noms de table au singulier car une table définit la structure pour 1 enregistrement type.Exemple : CommandeEnTete avec 2 majuscules, 0 espace, 0 accent et au singulier.F. Importer des données dExcel dans AccessLe menu Données Externes permet dimporter des données dorigines diverses vers Access.Attention : la structuration des données dans Excel est incompatible avec celle dAccess.On peut récupérer certaines informations dExcel dans Access mais un travail manuel est ensuitenécessaire pour restructurer ces informations suivant le modèle Relationnel utilisé par Access.Le transfert Excel  Access est souvent difficile.G. Exporter des données dAccess vers ExcelLe menu Données Externes permet dexporter des données vers diverses destinations comme Excel.Remarque : la structuration des données dans Excel est incompatible avec celle dAccess.On peut exporter des informations dAccess vers Excel.Le transfert Access  Excel est généralement facile. Ensat - Informatique Appliquée - Marc Souques Page 16
  • 17. - ACCESS 2007 – 2010 - Travail dirigé Facturation –4. Sécurité de lutilisation de lapplicationPar défaut, Access est configuré comme ci-dessous dans Bouton Office (Fichier dans version2010), Options Access, Centre de gestion de la confidentialité, Paramètres de centre de gestion dela confidentialité :Donc, à louverture dune application Access, vous pouvez voir ceci :En effet, une application Access (comme Excel, Word et Powerpoint) peut avoir un contenu (programmeVBA ou autres techniques) potentiellement dangereux.Donc, ici, du contenu est désactivé. Pour gérer cet aspect, plusieurs solutions : 1. Garder la configuration ci-dessus et cliquer à chaque ouverture de la base sur le bouton Option… puis choisir Activer le contenu si vous faite confiance à lorigine du fichier. 2. Modifier la configuration ci-dessus en activant loption Activer toutes les macros. Cest non recommandé et potentiellement dangereux car ainsi, pour toutes les applications, du code malsain peut être exécuté. 3. Garder la configuration ci-dessus pour le Paramètre des macros à Désactivé mais définir un dossier où vous rangerez les applications de confiance (les vôtres par exemple). Dans la fenêtre du Centre de gestion de la confidentialité, Emplacements approuvés, ajouter un nouvel emplacement pour un dossier (et éventuellement ses sous-dossiers) pour obtenir un résultat de ce type. Attention, cette opération peut poser problème sur un disque d’un réseau local protégé.Cest cette dernière solution qui est recommandée et que vous mettez en place.Valider ces paramètres et fermer Access.-Sous lapplication Explorateur de fichiers : - Supprimer éventuellement la base : essai.accdb - Copier Fac01a2007.accdb depuis le dossier de correction vers votre dossier de travail.- Ouvrir la base : Fac01a2007.accdb par double clic (la votre , pas celle donnée en correction)Cette application a les mêmes définitions que ci-dessus mais avec plus de données saisies dans lestables. Ensat - Informatique Appliquée - Marc Souques Page 17
  • 18. - ACCESS 2007 – 2010 - Travail dirigé Facturation –5. Utiliser les tables en mode InteractifA. Rechercher des donnéesOuvrir Table Client :Positionner le curseur sur le champ Nom et sur l’enregistrement 1:Clic sur le bouton de Recherche (Jumelles de longlet Accueil) : TERIEUR puis bouton Suivant1) Caractères génériques * ? #a) Astérisque : * (remplace une chaîne de caractères quelconque)Positionner le curseur sur le champ Nom et sur l’enregistrement 1:Rechercher : BON*Rechercher : *Rb) Point dinterrogation : ? (remplace un seul caractère quelconque)Positionner le curseur sur le champ Nom Client et sur l’enregistrement 1:Rechercher : ??Rc) Dièse : # (remplace un seul caractère-chiffre)Positionner le curseur sur le champ Adresse et sur l’enregistrement 1:Rechercher : 6#*Rechercher : 6* (comprendre la différence avec 6#*) Ce que vous cherchez bdB. Remplacer des données est ou pas une sous partie du champ entier ?Onglet Accueil > bouton de Recherche > Remplacer...Positionner le curseur sur le champ Adresse et sur l’enregistrement 1:Rechercher : bd (attention : rechercher dans "champ entier" ou autre manière ?)Remplacer par: boulevard a) en manuel : bouton Suivant pour chercher et bouton Remplacer pour modifier b) en automatique : bouton Remplacer toutRemettre bd à la place de boulevardC. Tester les boutons…1) Les 5 boutons de déplacement d’enregistrements en bas d’écranPremier , Précédent, Rang de lenregistrement courant , Suivant, Dernier, Nouvel enregistrement2) Les 2 boutons de tri croissant et décroissant Ensat - Informatique Appliquée - Marc Souques Page 18
  • 19. - ACCESS 2007 – 2010 - Travail dirigé Facturation –6. Définir les relations entre les tablesNous avons maintenant 4 tables, préparées pour être liées les unes aux autres. Avant de tirer lesRelations, il faut vérifier que toutes les tables sont fermées et non utilisées par des formulaires ourequêtes.Accéder à la fenêtre Relation :Onglet Outils de base de données/ Relations . Ci-dessus : travail à faire = lier les tables par des Relations ou JointuresAjouter (si besoin par bouton +Afficher la table ) les 4 tables: Client, CommandeEnTete,CommandeLigneProduit et Produit et fermer cette fenêtre dajout.1° lien : 1 Client (Père) peut avoir N CommandeEnTete (Fils) et 1 CommandeEnTete (Fils) est liée à 1 seul Client (Père) Définir des Liens ( appelés aussi Jointures) : * Glisser Num Client de Client (clé primaire) sur Ref Client de CommandeEnTete (clé étrangère). Boîte de dialogue :- cocher Appliquer Intégrité Référentielle, (vérifications des données des liens, voir TEST plus loin)- Ne pas cocher Mettre à jour en cascade- Ne pas cocher Effacer en cascade- Type de Jointure (consulter seulement) Créer EGAUX Un-à-plusieurs : cest le cas classique Un-à-un : erreur probableRemarque : le 1 et le ∞se placent automatiquement sur le lien,Le 1 à coté du champ clé primaire car ce champ est sans doublon,Le ∞à coté du champ clé étrangère car ce champ est doublons possibles.Pour une valeur de Num Client, on peut trouver N (∞ Commandes. )Pour une valeur de Ref Client, on ne peut trouver qu1 Client.2° lien :1 CommandeEnTete peut avoir N CommandeLigneProduit et1 CommandeLigneProduit est liée à 1 seule CommandeEnTete Définir des Liens ( appelés aussi Jointures) :* Glisser Num Commande de CommandeEnTete (clé primaire) sur Ref Commande de CommandeLigneProduit (clé étrangère)Remarque : Ref Commande nest pas un champ clé primaire (malgré le symbole clé ) mais la moitiédune clé primaire qui elle est composée des 2 champs Ref Commande et Ref Produit . Boîte de dialogue- cocher Appliquer Intégrité Référentielle (vérifications des données des liens, voir TEST plus loin)- Relation 1 à Plusieurs -Pas de Mise à jour et pas dEffacement en cascade- Type de Jointure (consulter seulement) Créer Ensat - Informatique Appliquée - Marc Souques Page 19
  • 20. - ACCESS 2007 – 2010 - Travail dirigé Facturation –3° lien :1 Produit peut avoir N CommandeLigneProduit et1 CommandeLigneProduit est liée à 1 seul Produit Définir des Liens ( appelés aussi Jointures) :* Glisser Num Produit de Produit (clé primaire) sur Ref Produit de CommandeLigneProduit (clé étrangère)Remarque : Ref Produit nest pas un champ clé primaire (malgré le symbole clé ) mais la moitié duneclé primaire qui elle est composée des 2 champs Ref Commande et Ref Produit . Boîte de dialogue- cocher Appliquer Intégrité Référentielle (vérifications des données des liens, voir TEST plus loin )- Relation 1 à Plusieurs-Pas de Mise à jour et pas d Effacement en cascade- Type de Jointure (consulter seulement) Créer Convention personnelle (à garder) pour nommer les champs des tables: Clé primaire mono-champ : Numxx (xx pour nom de cette table) Clé étrangère : Refxx (xx pour nom de la table liée)Remarque : On trouvera ailleurs que dans notre établissement des liens (jointures, relations) entre clé primaire et clé étrangère comme ceci par exemple :Numxx clé primaire de table-père et Numxx clé étrangère de table-fils ouCodexx clé primaire de table-père et Codexx clé étrangère de table-fils ouIdxx clé primaire de table-père et Idxx clé étrangère de table-fils ouRefxx clé primaire de table-père et Refxx clé étrangère de table-fils.Soit le même nom de champ pour la clé Primaire et Etrangère !!!Je nadhère pas, à titre pédagogique, à cette manière de nommer 2 choses différentes avec le même nommais il faut savoir que cette manière de nommer existe même si elle est ambigüe.Cela vient en partie du fait quil existe des logiciels (PowerAMC, WinDesign) qui, à partir dun MCD, génèrentautomatiquement un MLD Relationnel et que ces logiciels reprennent (faute de règle) automatiquement le nom dela clé primaire du MCD (lidentifiant pour être exact) pour créer la clé étrangère.Comme il ny a pas de règle pour nommer ces 2 types de clés, chacun fait avec sa règle.Vous trouverez donc toutes les combinaisons possibles de clé Primaire et Etrangère, lessentiel est debien comprendre le rôle différent de chaque clé :La Clé primaire : - identifie chaque enregistrement dune table. - est SANS doublon. - est souvent de type numérique et même Numauto (pour générer AUTOMATIQUEMENT un numéro). - est définie comme Primaire car clic surLa Clé étrangère : - pointe vers 1 enregistrement dune autre table. - est AVEC doublon possible (2 fois la valeur 1 car 2 Commandes peuvent pointer vers le mêmeNum Client 1). - est dun type qui dépend obligatoirement du type de la clé primaire pointée, JAMAIS Numautocar lobjectif est que lutilisateur choisisse MANUELLEMENT (ou par liste déroulante) unenregistrement de lautre table. - est définie comme Etrangère seulement parce que cest elle qui est liée à la clé primaire delautre table. Nous nommerons donc différemment clé primaire et clé étrangère. Ensat - Informatique Appliquée - Marc Souques Page 20
  • 21. - ACCESS 2007 – 2010 - Travail dirigé Facturation –A. Importance des relations dans un SGBD RelationnelAccess est un SGBD (Système de Gestion de Base de Données) Relationnel. Tout le fonctionnement dune application sous Access est basé sur les Relations entre les Tables.La définition des Tables et Relations représente les FONDATIONS de lapplication sur lesquelles ilsuffira ensuite de créer dautres objets bénéficiant de ces Relations.En France, la méthode la plus utilisée pour créer ces relations est la méthode Merise qui ne sera pasabordée ici. La méthode UML prend peu à peu le relais de la méthode Merise avec les mêmes principesconcernant la définition des Données. Rater la définition des Tables et Relations condamne lapplication à léchec !!!B. Des régles dans les relationsRègle 1 :Lun des 2 champs du lien est obligatoirement une clé primaire (Num xxx dans mes conventions).Lautre champ est obligatoirement une clé étrangère (Ref xxx dans mes conventions) donc, jamais 2 clés primaires reliées : Num Commande (1) n’a aucun rapport avec Num Produit (CAN001)Attention : certaines clés étrangères ont le symbole car elles sont sous partie dune clé primairecomposée de plusieurs champs : exemple : Ref ProduitRègle 2 :Pour qu’une relation (lien ou jointure) puisse être réalisée, les 2 champs liés doivent contenir desdonnées de même type et même longueur. Clé primaire (Num xxx) Clé étrangère (Ref xxx) (NumAuto) donc du Numérique entier Numérique, Entier long Texte, Longueur 6 Texte, Longueur 6 Numérique, Entier long Numérique, Entier longRègle 3 :Les valeurs du champ Ref xxx de la table1 doivent pouvoir être égales … au champ Num xxx de la table2. = Clé primaire (Num xxx) Clé étrangère (Ref xxx) Num Client de Client = 3 Ref Client de CommandeEnTete = 3 Num Produit de Produit = CAN001 Ref Produit de CommandeLigneProduit = CAN001Etablir un lien entre Num Client (1, 2, 3, 4,…) et Ref Produit (CAN001, EPS001, CAN002, …)na AUCUN sens car les valeurs ne seront jamais égales (=).Pour modifier ou Supprimer des relations : (Clic droit sur le trait 1-- )Enregistrer la mise en forme des relations et Fermer cette fenêtre Relation : IMPORTANT :Le travail réalisé par Accès au niveau des Relations consiste : Voir- à rechercher à partir dune valeur dune clé étrangère exemples lunique (1) valeur égale (=) dans la clé primaire liée. en bas de- à rechercher à partir dune valeur dune clé primaire page 5 dules (N) valeurs égales (=) dans la clé étrangère liée. Chapitre I Ensat - Informatique Appliquée - Marc Souques Page 21
  • 22. - ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Tester lIntégrité Référentielle des RelationsLIntégrité Référentielle est la vérification des données présentes dans la clé primaire etla clé étrangère dune Relation suite à une opération de saisie, modification ou suppression.1) Ouvrir la table CommandeEnTeteTenter de saisir dans Ref client de la table CommandeEnTeteune valeur nexistant pas dans Num Client de la table Client : 999 999 (et valider lenregistrement)  erreur car, dans la table Fils CommandeEnTete, saisir dans Ref Client la valeur 999 999 alorsque dans la clé primaire Num Client de la table Père "Client" la valeur 999 999 nexiste pas, cela nestpas valide. Un Fils ne peut PAS faire référence à un Père dont la valeur ne peut être retrouvée dans la table Père. Remarque : un fils peut ne pas faire référence à un père si on lautorise, une CommandeEnTetepourrait ne pas faire référence à un client (pas de saisie dans Ref Client) mais nous lavons interdit danscette application dans le champ Ref Client avec la propriété Null interdit (vide interdit) : OUI et Chainevide autorisé : NON donc ici la saisie est obligatoire.2) Ouvrir la table ProduitTenter de supprimer de la table Produit le produit CAN001 (clic dans la marge et bouton Suppr )alors que ce produit existe (est commandé) dans la table CommandeLigneProduit dans lechamp Ref Produit : CAN001.  erreur car supprimer dans la table Père une clé primaire CAN001 alors quau moins unenregistrement de la table Fils CommandeLigneProduit y fait référence par la clé étrangère Ref Produitest une suppression non valide. Un Père ne peut PAS être supprimé sil a encore au moins 1 Fils qui fait référence à lui même. Dans les cas 1) et 2), on ne veut pas dun Fils pointant vers un Père NON "trouvable". Voila ce que lintégrité référentielle doit éviter : Null interdit (vide interdit) : NON donc saisie facultative Fils de Père inconnu est autorisé ici. Utiliser le fichier Fac02a2007.accdb pour la suite. (Demander à Voir le diaporama "PV et sa Voiture") Ensat - Informatique Appliquée - Marc Souques Page 22
  • 23. - ACCESS 2007 – 2010 - Travail dirigé Facturation –7. Résumé des Tables  4 tables avec : Nom des champs, Clé primaire, Type de données et Propriétés de chaque champ (ici propriétés du champ fléché). Plus tard, "Liste de choix" avec liste déroulante  Des Relations entre les tables avec Intégrité référentielle  Des Données saisiesRemarque : Créer une application gérant des personnes nécessite lautorisation ou la déclaration à laCNIL (Commission Nationale Informatique et Liberté) :http://www.cnil.fr/vos-responsabilites/declarer-a-la-cnil/ Ensat - Informatique Appliquée - Marc Souques Page 23
  • 24. - ACCESS 2007 – 2010 - Travail dirigé Facturation –Ch3. Les requêtes de baseLa requête permet de faire des TRAITEMENTS sur les DONNEES des TABLES. Il existe plusieurstypes de Requête : la plus simple est celle qui sélectionne des enregistrements sur des critères.Possibilité de faire des requêtes qui ajoutent, modifient ou suppriment des enregistrements.1. Requête sélectionElle permet de sélectionner, trier, calculer des enregistrements sur une ou plusieurs tablesreliées entre elles.A. Requête simple sur une seule tableCréer une Nouvelle requête, onglet Créer, bouton Création de requête basée sur la table Client.1) Requête "R01 Client Ou"Question : "Liste des clients avec visualisation des champs Nom, Prénom, Date Entrée et Ville, parordre alphabétique de leur nom puis prénom, habitant Clamart OU nommés TERIEUR "Saisir les noms des champs ci-dessous par double-clic dans la table affichée en haut de l’écran. Champ: Nom Prénom Date Entrée Ville Table: Client Client Client Client Tri: Croissant Croissant Afficher: X X X X Critères: clamart Ou = entre 2 lignes Ou: TERIEUR de colonnes quelconquesPour une saisie dans un champ de type TEXTE, les " sont ajoutés comme délimiteur "TERIEUR"Exécuter la requête (Afficher les résultats) par le bouton puisrevenir en mode création (Définition de la requête en tableau) par le bouton puispasser en mode SQL (Définition de la requête en langage SQL = Structured Query Langage) par clicsur Affichage sous puis par le bouton SELECT Client.Nom, Client.Prénom, Client.[Date Entrée], Client.Ville FROM Client WHERE (Client.Nom = "TERIEUR") OR (Client.Ville = "CLAMART") ORDER BY Client.Nom, Client.Prénom ;SQL est un langage Standard pour tous les Systèmes de Gestion de Bases de Données pour créer,interroger et manipuler les Bases de Données (BD). La commande de base permet laSélection de Champs de Tables où des conditions sont requises, ordonnés par des critères de tri. SELECT liste de champs FROM tables WHERE conditions de sélection ORDER BY critères de triDans Access, dans un premier temps, nous naurons pas besoin de la syntaxe SQL pour créer une requêtecar nous bénéficions du mode Création de la requête sous forme de tableau interactif.Si vous souhaitez vous formez au langage SQL, je vous propose mon Polycopié à demander : SQL pour SGBD.pdfPasser en mode Création :Enregistrer la requête : R01 Client Ou et fermer cette requête. Ensat - Informatique Appliquée - Marc Souques Page 24
  • 25. - ACCESS 2007 – 2010 - Travail dirigé Facturation –2) Requête "R02 Client Et"Question : "Liste des clients nommés TERIEUR et habitant CLAMART" Champ: Nom Ville Ville Table: Client Client Client Tri: Afficher: X X X Critères: TERIEUR CLAMART Différence entre Ou: ces 2 lignes CLAMARTExécuter la requête : Et = entre 2 colonnes de la même lignePasser en mode Création :Enregistrer la requête : R02 Client Et et fermer cette requête.3) Requête "R03 Client Entre"Question : "Liste décroissante des noms des clients inscrits entre le 15/4/2009 et le 1/5/2009" Champ: Nom Date Entrée Table: Client Client Tri: Décroissant Afficher: X X Critères: entre 15/4/2009 et 1/5/2009Pour une saisie dans un champ de type DATE, les # sont ajoutés automatiquementcomme délimiteur de date= #15/04/2009#Opérateurs de comparaison : (faute d’opérateur choisi, c’est l’opérateur = qui est utilisé par défaut) = , > , < , >= , <= , < > (différent), Comme (comparaison avec caractères joker ci-dessous)Possibilité dutiliser les caractères Jokers vus précédemment (pour les champs de type Texte) : * , ? , # avec lopérateur Comme pour interpréter ces JokersExécuter la requête, Passer en mode Création :Enregistrer la requête : R03 Client Entre et fermer cette requête.4) Requête "R04 Client Comme"Question : "Recherche des clients dont le Nom commence par la lettre T et inscrits après le 10/4/ delannée 2009. Afficher aussi la ville " Champ: Nom Date Entrée Ville Table: Client Client Client Tri: Afficher: X X X Critères: T* >10/04/2009 ATTENTION : Ou: Ici, la saisie de "T*" génère automatiquement "Comme T*" mais parfois vous devrez saisir manuellement le Comme pour utiliser * ? ou #Exécuter la requête, Passer en mode CréationPossibilités de dimensionner les colonnes, déplacer une colonne, insérer une colonne, insérer une lignede critères, supprimer une colonne.Enregistrer la requête : R Client Comme. Fermer la fenêtre des requêtes :Exécuter la requête R Client Comme. Ensat - Informatique Appliquée - Marc Souques Page 25
  • 26. - ACCESS 2007 – 2010 - Travail dirigé Facturation –5) Exercices à faire A FAIRE…1 : nommer la requête R05Question : "Recherche des produits dont[le Prix est inférieur à 500 €et dont la Désignation commence par "imp" (en minuscule ou majuscule)]ou bien [dont le taux de TVA est supérieur ou égal à 15% (0.15) et dont le Num Produit a un "A" en 2ième position et un "0" (zéro) en 5 ième positionPenser à utiliser l’opérateur Comme " " si vous utilisez des caractères ?, *, #. Classer par désignation. " A FAIRE…2 : nommer la requête R06Question : "Recherche des clients dont[la Date d’entrée est postérieure au 15 Avril 2009 et habitant le département 93] ou bien [des Toulousains prénommés Jean]. Classer par Nom. " A FAIRE…3 : nommer la requête R07Question : "Recherche des clients dont[la Date d’entrée est Mai (5)] et qui ne sont pas toulousain.6) Requête "R08 Commande Mono Table "Question : "Recherche, dans la seule table CommandeEnTete, des Commandes des clients 3, 6 et 34dont les commandes ne sont pas payées. Afficher tous les champs, trier par client." « A vous dessayer... »Commentaire :Les résultats ne font apparaître aucune des informations du client (nom, prénom, …) hors mis le numéro.Ces données sont dans les tables Client.Nous allons donc faire des requêtes multi-table pour visualiser les informations client depuis unecommande. Ensat - Informatique Appliquée - Marc Souques Page 26
  • 27. - ACCESS 2007 – 2010 - Travail dirigé Facturation –B. Requête multitable simple avec Relation1) Requête "R09 Commande et son Client"Question : " Liste des Commandes (seulement len tête) avec détail de son Nom et Prénom Client."Nous avons lié les Clients et les Commandes dans la fenêtre relation.Ce lien peut être utilisé dans 2 sens :- Pour 1 Commande, jobtiens son Unique Client- Pour 1 Client, jobtiens ses N (∞ Commandes. )Définir une requête basée sur les 2 tables CommandeEnTete et Client avec les champs suivants. = =Ce résultat affiche les Commandes pour lesquelles Ref Client = Num ClientVoir le mode SQL de la requête : SELECT [Num Commande], [Date Commande], [Date Echéance],[Ref Client], [Num Client], Nom, Prénom FROM Client INNER JOIN CommandeEnTete ON Client.[Num Client] = CommandeEnTete.[Ref Client]; Clé primaire Clé étrangère AVEC doublons SANS doublon de table "Père" de table "Fils"Attention : Toutes les commandes peuvent ne pas être affichées. Si …le champ Ref Client de CommandeEnTete est vide, Access ne trouve pas un Num Client avec contenuvide EGAL (=) car une clé primaire (Num Client ) ne peut être vide.Ici, toutes les commandes sont affichées car le champ Ref Client esten Null interdit = OUI et Chaine vide autorisée = NON donc saisie obligatoire et jamais effacé. Dans une relation Père - Fils, les Fils pointant vers aucun Père (pas de saisie) ne sont pas affichés dans une requête basée sur les 2 tables liées. - Ce sont des Fils de Père inconnu -Ce Problème peut être géré en modifiant le type jointure sur laRelation Clé primaire - Clé Etrangère.Nous ne le verrons pas pour le moment. Ensat - Informatique Appliquée - Marc Souques Page 27
  • 28. - ACCESS 2007 – 2010 - Travail dirigé Facturation –2) Requête "R10 Client et ses Commandes" Demander à voir leQuestion : " Liste des Clients avec leurs Commandes (seulement len tête)". Formulaire Client et ses commandes FPNous avons lié les Clients et les Commandes dans la fenêtre relation.Ce lien peut être utilisé dans 2 sens :Pour 1 Client, jobtiens ses N (∞ Commandes. )Pour 1 Commande, jobtiens son Unique ClientDéfinir une requête basée sur les 2 tables Client et CommandeEnTete avec les champs suivants. = =Ce résultat affiche les Clients avec Num Client = Ref ClientVoir le mode SQL de la requête : SELECT Nom, Prénom, [Num Client], [Ref Client], [NumCommande], [Date Commande], [Date Echéance] FROM Client INNER JOIN CommandeEnTete ON Client.[Num Client] = CommandeEnTete.[Ref Client]; Clé primaire SANS doublon Clé étrangère "Père" AVEC doublons "Fils"Attention : Tous les clients peuvent ne pas être affichées. Si …certaines valeurs de Num Client ne sont pas saisies dans le champ Ref Client de CommandeEnTete,Access ne trouve pas légalité clé primaire = clé étrangère.Ici, le client 2 nest pas affiché car aucun enregistrement de Ref Client ne contient la valeur 2.Par contre, le client 3 DURAND est affiché plusieurs fois car plusieurs enregistrements du champRef Client contiennent la valeur 3. DURAND possède N commandes : la 1 et la 2. Dans une relation Père - Fils, les Pères nayant aucun Fils pointant sur eux ne sont pas affichés dans une requête basée sur les 2 tables liées. Ce sont des Pères potentiels sans Fils.Ce Problème peut être géré en modifiant le Typejointure sur la Relation Clé primaire - Clé Etrangère.Nous ne le verrons pas pour le moment. Ensat - Informatique Appliquée - Marc Souques Page 28
  • 29. - ACCESS 2007 – 2010 - Travail dirigé Facturation –C. Requête multitable avec calcul par enregistrement1) Requête "R11 Produits commandés et calcul"Question : " Liste des Produits commandés dans les Commandes avec calcul Total HT à payer (prixHT* quantité), Montant de la TVA et Montant TTC de la ligne de commande éventuellement remisé".a) Gérer tables et champs de tableOn peut créer une requête avec les 4 tables liées ( = ) et les champs suivants. = = =Mais il nest pas obligatoire de présenter les paires de champs liés. Simplifier.On simplifie encore (pour faire de la place sur ce document pour les futurs calculs).Enregistrer : R11 Produits commandées et calculs (Enregistrement OBLIGATOIRE pour la suite) Ensat - Informatique Appliquée - Marc Souques Page 29