Département Génie Informatique BD40 Ingénierie des systèmes d’information TP 3 : Ms/Access : Requêtes et Formulaires Thème :Base de données hospitalière Christian FISCHER Copyright © Octobre 2006 Contexte de travail : Après avoir modélisé la gestion des consultations des patients dont le modèle conceptuel de données, utilisant le formalisme entité-association, est fourni cidessous : vous êtes chargé de valider ce premier travail avant d’envisager le développement d’une application informatique grâce au logiciel MS-Access version 2000. Travail à réaliser A. Développement de requêtes Étape 1 : Etude de la base de données existante Ouvrir la base de données MEDICAL_TP3_DATA.MDB Afficher le nombre de lignes de chaque table Étape 2 : Séparation des données et des requêtes dans deux bases distinctes 1. Créer une nouvelle base de données vierge nommée : MEDICAL_TP3_APPLI.MDB 2. Attacher toutes les tables de la base de données MEDICAL_TP3_DATA.MDB A l’aide du menu Fichier/Données Externes/Attacher les tables ou Lier les tables Lier les 4 tables de la base de données Étape 3 : Modification des données de la base de données A partir de la base de données MEDICAL_TP3_APPLI.MDB 1. Création de 5 nouveaux patients (numéros de 6 à 10) 2. Création de 2 nouveaux médecins 3. Création de 5 nouvelles consultations : la date de consultation doit correspondre à la date du jour Étape 4 : Création de requêtes de sélection avec l’assistant Pour chaque requête, afficher le code SQL de la requête. Modifier toujours l’entête de chaque colonne résultat de la requête pour afficher des « noms métiers ». Exemple de résultat avec la codification des premières requêtes 4.1. Requête de projection : R01 Projection Patients Afficher le numéro, le nom et le prénom des patients Modifier les entêtes de colonnes (alias de colonne) de manière à obtenir l’affichage suivant : Enregistrer chaque requête avec le nom complet : "R01 Projection Patients" 4.2. Requête de projection avec suppression des doublons : "R02 Projection Prénom Patients" Afficher les prénoms différents Modifier la propriété de requête, pour supprimer l’affichage répétitif du même prénom. 4.3. Requêtes de restriction R03A : "Médecins ayant le code spécialité 1" R03B : "Médecins dont la date d’entrée est comprise entre le 1/1/1998 et le 31/12/1998" R03C : "Médecins dont le nom commence par Du" R03D : "Patients résidant dans le département 90 et dont la date de naissance est antérieure au 1/1/90" 4.4. Requêtes de restriction avec des tris R04A : Consultations par date décroissante (de la plus récente à la plus ancienne) R04B : Liste des patients triée par Nom et prénom 4.5. Requêtes de restriction avec un palmarès R05 : 6 dernières consultation Effectuer un test avec les 3 dernières consultations. Quel constat pouvez-vous faire ? 4.6. Requêtes de jointures R06A : Toutes les informations du médecin et nom de sa spécialité R06B : Pour chaque consultation afficher le nom du patient, le nom du médecin et la date de consultation R06C : Liste des consultations avec le résultat suivant : Étape 5 : Création de requêtes en mode SQL Procédure à suivre : 1. Création d’une nouvelle requête en mode création 2. Sélection des tables nécessaires pour la requête 3. Affichage du mode SQL 4. Rédaction de la requête en langage SQL 5. Sauvegarde de la requête 6. Exécution de la requête 7. Affichage de la requête en mode Création R07A : Afficher le nom et prénom des patients R07B : Afficher les patients résidants à Paris R07C1 et R07C2 : Afficher le numéro, le nom du médecin et le nom de sa spécialité avec un tri sur le nom du médecin. Utiliser pour chaque requête une des syntaxes de jointure présentées dans le cours. 1. La première syntaxe est basée sur la syntaxe ANSI avec l'utilisation du mot clé INNER JOIN. 2. La seconde syntaxe est basée sur l'utilisation d'un produit cartésien et d'une sélection permettant de définir la condition de jointure (utilisation de JOIN interdite). Étape 6 : Création de requêtes avancées en mode QBE ou SQL Fonctions agrégatives et regroupement R08A : Calculer le nombre de patients dans la base de données R08B : Calculer le nombre de médecins par spécialité R08C : Calculer le nombre de consultations par patients : Extrait du résultat : Fonctions agrégatives, regroupement et filtrage R08D : Calculer le nombre de consultations par patients. Afficher uniquement les patients ayant au moins 3 consultations Étape 7 : Création de requêtes paramétrées en mode QBE R09A : Afficher les patients ayant consulté un médecin dont le nom de médecin est saisi à l’aide du paramètre suivant : [Quel nom de médecin ?] Afficher : le numéro de patient, le nom et le prénom du patient, la spécialité du médecin et la date de la consultation. Effectuer le test avec le médecin : Dupont Effectuer le test avec le médecin : Du Si nécessaire modifier le paramétrage pour obtenir un résultat dans les deux cas. R09B : Afficher les informations sur les consultations à la date à saisir en paramètre. Le nom du paramètre sera : [Quelle date de consultation?] Étape 8 : Création d’une requête de jointure externe en mode QBE Ouvrir la requête R06A. Enregistrer la requête sous le nom "R10 Tous les Médecins et Spécialité associée". Dans la base de données : MEDICAL_TP3_DATA.MDB Modifier la définition de l’intégrité référentielle entre le médecin et la spécialité de manière à obtenir tous les médecins dans le résultat de la requête, même si le médecin ne possède pas de spécialité. Cette modification s'applique-t-elle aux anciennes requêtes ? Consulter en particulier la requête R06A. Ouvrir la requête R10 et modifier le type de jointure dans la requête. Consulter le code SQL Exécuter la requête. Quel constat pouvez-vous effectuer ? Est-il possible de modifier le type de jointure dans une requête sans que cette modification en soit prise en compte dans les relations entre les tables ? Étape 9 : Création de requêtes d’action R11A : Insertion d’un patient Créer une nouvelle requête d’action permettant d’insérer un nouveau patient. Utiliser le mode SQL avec des paramètres : INSERT INTO PATIENT ( NUMPATIENT, PRENOMPATIENT, NOMPATIENT, NUMINSEE, DATENAISSANCE, ADRESSERUE, CODEPOSTAL, ADRESSEVILLE ) VALUES ([Numpatient ?], [Prenom Patient ?], [nom ?], [insee ?], [Naissance ?], [rue ?], [Cp ?], [Ville ?]); R11B : Suppression des consultations du 12/3/99. R11C : Requête de mise à jour : Modification du code postal des patients résidant à Bavilliers. La nouvelle sera 90851. B. Développement de formulaires Étape 1 : Création de formulaires à l’aide de l’assistant 1. Création de formulaires instantané pour les quatre tables de base de données Enregistrer ces quatre formulaires sous le nom FrmINomtable 2. Création d’une formulaire gabarit (ou template) suivant un "style" donné Présentation du formulaire à créer : Créer les boutons de commandes à l’aide de l assistant de création de boutons de commandes. Enregistrer le formulaire sous le nom FrmGabarit 3. Création de formulaires à l’aide du gabarit Pour chaque table créer un nouveau formulaire FrmGNomtable en suivant la procédure suivante a) Copier le formulaire Frmgabarit b) Coller le formulaire, puis renommer le formulaire FrmGNomTable dans la boîte de dialogue Coller. Remarque : vous pouvez également ouvrir le formulaire FrmGabarit, puis l’eneregistrer sous un nom différent. c) Ouvrir le formulaire FrmGNomTable en mode Création présente de la manière suivante : . L’interface se On constate que la source de données du formulaire est vide d) Définition de la source de données du formulaire Doucle clic sur le carré noir dans le coin supérieur gauche du formulaire pour accéder aux propriétés du formualire. Dans la boîte de dialogue Formulaire, sélectionner l’onglet Données. Dans le champ Source, utilisez la liste déroulante pour sélectionner la table concernée par le formulaire. Dans l’exemple, ci-dessous, la sélection de la table Spécialité a été effectuée. Fermer la boîte de dialogue. Vérifier l’affichage des colonnes dans l’interface graphique. e) Affectation d’une colonne de la table à la zone de texte. Accéder à la boîte de dialogue de propriétés de la zone de texte. Dans l’onglet Données, sélectionner dans la source du contrôle la colonne de table que vous souhaitez affecter au contrôle. Fermer la boîte de dialogue. Modifier l’étiquette. Résultat attendu e) Pour tous les champs de la table à afficher, 1. Sélectionner la première étiquette et la zone de texte associée 2. Copier et coller les deux contrôles 3. Déplacer les contrôles 4. Modifier l’étiquette 5. Modifier la source du contrôle en sélectionnant le champ de la table. Résultat : f) Modifier le titre du formulaire et le nom de la fenêtre. g) Enregistrer le formulaire modifié. Vérifier l’affichage des données. Étape 2 : Création de formulaires en mode création 4. Créer un formulaire FrmAPropos Étape 3 : Création des menus 5. Création de la barre de menus : MenuGestionHopital 5.1. A partir du menu Outils, option Personnaliser, créer une barre de menus La barre d’outils vide s’affiche A l’aide de la commande Nouveau Menu, création des 5 menus principaux de la barre de menus 5.2. Création du menu Fichier Personnaliser la barre d’outils MenuGestionHopital. Afficher la catégories Fichier, puis sélectionner les commandes présentées dans le menu. Création d’un groupe de menu pour les 3 premières options. 5.3. Création du menu Requêtes Ce menu doit permettre d’appeler toutes les requêtes développées. 5.4. Création du menu Formulaires Ce menu doit permettre d’appeler tous les formulaires développés. 5.5. Création du menu Fenêtre Menu classique Windows 5.6. Création du menu ? Contient l’ouverture de la fenêtre A propos.