Microsoft Access est sans doute l'outil le plus puissant de toute la suite Microsoft Office, mais il mystifie (et parfois effraie) les utilisateurs d'Office. Avec une courbe d'apprentissage plus raide que Word ou Excel, comment quelqu'un est-il supposé se servir de cet outil? Cette semaine, Bruce Epper se penchera sur certains des problèmes suscités par cette question d'un de nos lecteurs.
Un lecteur demande:
Je rencontre des problèmes lors de l'écriture d'une requête dans Microsoft Access.
J'ai une base de données avec deux tables de produits contenant une colonne commune avec un code de produit numérique et un nom de produit associé.
Je veux savoir quels produits du tableau A peuvent être trouvés dans le tableau B. Je veux ajouter une colonne nommée Results qui contient le nom du produit du tableau A s'il existe, et le nom du produit du tableau B quand il n'existe pas dans le tableau A.
Avez-vous des conseils?
Réponse de Bruce:
Microsoft Access est un système de gestion de base de données (SGBD) conçu pour être utilisé sur les ordinateurs Windows et Mac. Il utilise le moteur de base de données Jet de Microsoft pour le traitement et le stockage des données. Il fournit également une interface graphique pour les utilisateurs qui élimine presque le besoin de comprendre le langage SQL (Structured Query Language).
SQL est le langage de commande utilisé pour ajouter, supprimer, mettre à jour et renvoyer des informations stockées dans la base de données ainsi que pour modifier des composants de base de données tels que l'ajout, la suppression ou la modification de tables ou d'index.
Point de départ
Si vous ne connaissez pas déjà Access ou un autre SGBDR, je vous suggère de commencer par ces ressources avant de continuer:
- Alors qu'est-ce qu'une base de données? Alors, qu'est-ce qu'une base de données? [MakeUseOf explique] Alors, qu'est-ce qu'une base de données? [MakeUseOf explique] Pour un programmeur ou un passionné de technologie, le concept d'une base de données est quelque chose qui peut vraiment être pris pour acquis. Cependant, pour beaucoup de gens, le concept d'une base de données elle-même est un peu étranger .... Lire la suite où Ryan Dube utilise Excel pour montrer les bases des bases de données relationnelles.
- Un guide rapide pour commencer avec Microsoft Access 2007 Un guide rapide pour démarrer Microsoft Access 2007 Un guide rapide pour démarrer Microsoft Access 2007 En savoir plus qui est un aperçu de haut niveau d'Access et les composants qui composent une base de données Access.
- Un didacticiel rapide sur les tables dans Microsoft Access 2007 Un didacticiel rapide sur les tables dans Microsoft Access 2007 Un didacticiel rapide sur les tables dans Microsoft Access 2007 En savoir plus examine la création de votre première base de données et des tables pour stocker vos données structurées.
- Un didacticiel rapide sur les requêtes dans Microsoft Access 2007 Un didacticiel rapide sur les requêtes dans Microsoft Access 2007 Un didacticiel rapide sur les requêtes dans Microsoft Access 2007 Lire la suite examine les moyens de renvoyer des portions spécifiques des données stockées dans les tables de base de données.
Avoir une compréhension de base des concepts fournis dans ces articles rendra ce qui suit un peu plus facile à digérer.
Relations avec la base de données et normalisation
Imaginez que vous dirigez une entreprise vendant 50 différents types de widgets dans le monde entier. Vous avez une base de clients de 1.250 et dans un mois moyen vendre 10.000 widgets à ces clients. Vous utilisez actuellement une seule feuille de calcul pour suivre toutes ces ventes - en fait, une seule table de base de données. Et chaque année ajoute des milliers de lignes à votre feuille de calcul.
Les images ci-dessus font partie de la feuille de calcul de suivi des commandes que vous utilisez. Maintenant, disons que ces deux clients achètent des widgets de vous plusieurs fois par an, donc vous avez beaucoup plus de rangées pour les deux.
Si Joan Smith épouse Ted Baines et prend son nom de famille, chaque rangée qui contient son nom doit maintenant être changée. Le problème est aggravé si vous avez deux clients différents avec le nom «Joan Smith». Il est devenu beaucoup plus difficile de garder vos données de vente cohérentes en raison d'un événement assez commun.
En utilisant une base de données et en normalisant les données, nous pouvons séparer les éléments en plusieurs tables telles que l'inventaire, les clients et les commandes.
En regardant simplement la partie client de notre exemple, nous supprimerions les colonnes pour le nom du client et l'adresse du client et les placerions dans une nouvelle table. Dans l'image ci-dessus, j'ai également amélioré les choses pour un accès plus granulaire aux données. La nouvelle table contient également une colonne pour une clé primaire (ClientID) - un nombre qui sera utilisé pour accéder à chaque ligne de cette table.
Dans la table d'origine où nous avons supprimé ces données, nous ajouterions une colonne pour une clé étrangère (ClientID) qui correspond à la ligne appropriée contenant les informations pour ce client particulier.
Maintenant, lorsque Joan Smith change son nom pour Joan Baines, le changement ne doit être fait qu'une seule fois dans la table Client. Toute autre référence provenant de tables jointes tirera le bon nom de client et un rapport qui regarde ce que Joan a acheté au cours des 5 dernières années recevra toutes les commandes sous ses deux prénoms et mariés sans avoir à changer la façon dont le rapport est généré .
Comme un avantage supplémentaire, cela réduit également la quantité totale de stockage consommée.
Types de jointure
SQL définit cinq types différents de jointures: INTÉRIEUR, GAUCHE EXTÉRIEUR, DROIT EXTÉRIEUR, FULL OUTER et CROSS. Le mot clé OUTER est facultatif dans l'instruction SQL.
Microsoft Access permet l'utilisation de INNER (par défaut), LEFT OUTER, RIGHT OUTER et CROSS. FULL OUTER n'est pas supporté en tant que tel, mais en utilisant LEFT OUTER, UNION ALL, et RIGHT OUTER, il peut être falsifié au prix de plus de cycles CPU et d'opérations d'E / S.
La sortie d'une jointure CROSS contient chaque ligne de la table de gauche associée à chaque ligne de la table de droite. La seule fois où j'ai vu une jointure CROSS utilisée est pendant le test de charge des serveurs de base de données.
Jetons un coup d'oeil à la façon dont les jointures de base fonctionnent, puis nous les modifierons pour répondre à nos besoins.
Commençons par créer deux tables, ProdA et ProdB, avec les propriétés de conception suivantes.
NuméroAuto est un entier long incrémenté automatiquement affecté aux entrées lorsqu'elles sont ajoutées à la table. L'option Texte n'a pas été modifiée, elle accepte donc une chaîne de texte de 255 caractères maximum.
Maintenant, remplissez-les avec des données.
Pour montrer les différences dans le fonctionnement des 3 types de jointure, j'ai supprimé les entrées 1, 5 et 8 de ProdA.
Ensuite, créez une nouvelle requête en accédant à Créer> Conception de requête . Sélectionnez les deux tables dans la boîte de dialogue Afficher la table et cliquez sur Ajouter, puis sur Fermer .
Cliquez sur ProductID dans la table ProdA, faites-le glisser vers ProductID dans la table ProdB et relâchez le bouton de la souris pour créer la relation entre les tables.
Cliquez avec le bouton droit sur la ligne entre les tables représentant la relation entre les éléments et sélectionnez Propriétés de la jointure .
Par défaut, le type de jointure 1 (INNER) est sélectionné. L'option 2 est une jointure LEFT OUTER et 3 est une jointure RIGHT OUTER.
Nous regarderons d'abord la jointure interne, donc cliquez sur OK pour fermer la boîte de dialogue.
Dans le concepteur de requêtes, sélectionnez les champs que nous souhaitons voir dans les listes déroulantes.
Lorsque nous exécutons la requête (le point d'exclamation rouge dans le ruban), le champ ProductName des deux tables s'affiche avec la valeur de la table ProdA dans la première colonne et ProdB dans la seconde.
Notez que les résultats affichent uniquement des valeurs où ProductID est égal dans les deux tables. Même s'il existe une entrée pour ProductID = 1 dans la table ProdB, elle n'apparaît pas dans les résultats puisque ProductID = 1 n'existe pas dans la table ProdA. La même chose s'applique à ProductID = 11. Il existe dans la table ProdA mais pas dans la table ProdB.
En utilisant le bouton Affichage du ruban et en basculant vers SQL View, vous pouvez voir la requête SQL générée par le concepteur utilisé pour obtenir ces résultats.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Pour revenir en mode Création, modifiez le type de jointure en 2 (LEFT OUTER). Exécutez la requête pour voir les résultats.
Comme vous pouvez le voir, chaque entrée dans la table ProdA est représentée dans les résultats alors que seules celles de ProdB qui ont une entrée ProductID correspondante dans la table ProdB apparaissent dans les résultats.
L'espace vide dans la colonne ProdB.ProductName est une valeur spéciale (NULL) car il n'existe pas une valeur correspondante dans la table ProdB. Cela s'avérera important plus tard.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Essayez la même chose avec le troisième type de jointure (RIGHT OUTER).
Les résultats montrent tout à partir de la table ProdB alors qu'il affiche des valeurs vides (NULL) où la table ProdA n'a pas de valeur correspondante. Jusqu'à présent, cela nous rapproche le plus des résultats souhaités dans la question de notre lecteur.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Utilisation de fonctions dans une requête
Les résultats d'une fonction peuvent également être renvoyés dans le cadre d'une requête. Nous voulons qu'une nouvelle colonne nommée "Résultats" apparaisse dans notre jeu de résultats. Sa valeur sera le contenu de la colonne ProductName de la table ProdA si ProdA a une valeur (ce n'est pas NULL), sinon elle doit être extraite de la table ProdB.
La fonction IF immédiate (IIF) peut être utilisée pour générer ce résultat. La fonction prend trois paramètres. La première est une condition qui doit être évaluée à une valeur True ou False. Le deuxième paramètre est la valeur à renvoyer si la condition est True et le troisième paramètre est la valeur à renvoyer si la condition est False.
La construction de la fonction complète pour notre situation ressemble à ceci:
IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) Notez que le paramètre condition ne vérifie pas l'égalité. Une valeur nulle dans une base de données n'a pas de valeur qui puisse être comparée à toute autre valeur, y compris une autre valeur nulle. En d'autres termes, Null n'est pas égal à Null. Déjà. Pour passer cela, nous vérifions plutôt la valeur en utilisant le mot-clé 'Is'.
Nous aurions pu aussi utiliser 'Is not Null' et changer l'ordre des paramètres True et False pour obtenir le même résultat.
Lorsque vous mettez ceci dans le concepteur de requêtes, vous devez taper la fonction entière dans le champ: entrée. Pour l'obtenir pour créer la colonne 'Résultats', vous devez utiliser un alias. Pour ce faire, préférez la fonction avec 'Résultats:' comme sur la capture d'écran suivante.
Le code SQL équivalent pour ce faire serait:
SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Maintenant, lorsque nous exécutons cette requête, elle produira ces résultats.
Ici, nous voyons pour chaque entrée où la table ProdA a une valeur, cette valeur est reflétée dans la colonne Résultats. S'il n'y a pas d'entrée dans la table ProdA, l'entrée de ProdB apparaît dans Results, ce qui est exactement ce que notre lecteur a demandé.
Pour plus de ressources pour apprendre Microsoft Access, consultez Comment apprendre Microsoft Access de Joel Lee: 5 Ressources en ligne gratuites Comment apprendre Microsoft Access: 5 Ressources en ligne gratuites Comment apprendre Microsoft Access: 5 Ressources en ligne gratuit Avez-vous de gérer une grande quantité de données? Vous devriez regarder dans Microsoft Access. Nos ressources d'étude gratuites peuvent vous aider à démarrer et apprendre les compétences pour des projets plus complexes. Lire la suite .