Demandez aux experts

Comment écrire des requêtes SQL Microsoft Access à partir de zéro

Comment écrire des requêtes SQL Microsoft Access à partir de zéro / Demandez aux experts

Microsoft Access est sans doute l’outil le plus puissant de toute la suite Microsoft Office, mais il mystifie (et parfois effraie) les utilisateurs expérimentés d’Office. Avec une courbe d'apprentissage plus abrupte que Word ou Excel, comment une personne est-elle censée se familiariser avec l'utilisation de cet outil? Cette semaine, Bruce Epper examinera certaines des questions soulevées par cette question d'un de nos lecteurs..

Un lecteur demande:

Je ne parviens pas à écrire une requête dans Microsoft Access.

J'ai une base de données avec deux tables de produit contenant une colonne commune avec un code de produit numérique et un nom de produit associé.

Je souhaite savoir quels produits du tableau A se trouvent dans le tableau B. Je souhaite ajouter une colonne nommée Résultats contenant le nom du produit du tableau A, s'il existe, et le nom du produit du tableau B, s'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 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 aux utilisateurs une interface graphique 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 les composants principaux de la 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 poursuivre:

  • Alors, quelle est une base de données? Alors, quelle est une base de données, de toute façon? [MakeUseOf explique] Alors, quelle est une base de données, de toute façon? [MakeUseOf explique] Pour un programmeur ou un passionné de technologie, le concept de base de données est quelque chose qui peut vraiment être pris pour acquis. Cependant, pour beaucoup de gens, le concept de base de données est un peu étranger… Read More où Ryan Dube utilise Excel pour montrer les bases des bases de données relationnelles.
  • Guide de démarrage rapide de Microsoft Access 2007 Guide de démarrage rapide de Microsoft Access 2007 Guide de démarrage rapide de Microsoft Access 2007 En savoir plus, un aperçu de haut niveau de Access et des composants qui composent une base de données Access..
  • Didacticiel rapide sur les tables dans Microsoft Access 2007 Didacticiel rapide sur les tables dans Microsoft Access 2007 Didacticiel rapide dans les tables dans Microsoft Access 2007 Read More examine la création de votre première base de données et des premières tables pour stocker vos données structurées..
  • Didacticiel rapide sur les requêtes dans Microsoft Access 2007 Didacticiel rapide sur les requêtes dans Microsoft Access 2007 Didacticiel rapide sur les requêtes dans Microsoft Access 2007 En savoir plus examine les moyens de renvoyer des parties spécifiques des données stockées dans les tables de la 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 de base de données et normalisation

Imaginez que vous dirigiez une entreprise vendant 50 types de widgets différents dans le monde entier. Vous avez une clientèle de 1 250 personnes et, au cours d’un mois moyen, vous vendez 10 000 widgets à ces clients. Vous utilisez actuellement une seule feuille de calcul pour faire le suivi de toutes ces ventes - en réalité, 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. Supposons maintenant que ces deux clients vous achètent des widgets plusieurs fois par an, de sorte que vous disposiez de beaucoup plus de lignes pour les deux..

Si Joan Smith épouse Ted Baines et prend son nom de famille, chaque rangée contenant son nom doit maintenant être modifiée. Le problème est aggravé si vous avez deux clients différents portant le nom de «Joan Smith». Il est devenu beaucoup plus difficile de maintenir la cohérence de vos données de vente en raison d'un événement assez courant..

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 Nom du client et Adresse du client et les placerions dans une nouvelle table. Dans l'image ci-dessus, j'ai également mieux réparti les choses pour un accès plus granulaire aux données. La nouvelle table contient également une colonne pour une clé primaire (ClientID) - un numéro 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 renvoie à la ligne appropriée contenant les informations relatives à ce client particulier..

Désormais, lorsque Joan Smith change de nom et devient Joan Baines, le changement ne doit être effectué qu'une fois dans la table Client. Toutes les autres références des tables jointes indiqueront le nom du client. Un rapport qui examine les achats de Joan au cours des 5 dernières années obtiendra toutes les commandes sous ses noms de jeune fille et de épouse sans avoir à modifier le mode de génération du rapport..

En outre, cela réduit également la quantité totale de stockage utilisée..

Types de jointures

SQL définit cinq types de jointures: INNER, LEFT OUTER, RIGHT OUTER, 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 pris en charge en tant que tel, mais en utilisant les options LEFT OUTER, UNION ALL et RIGHT OUTER, il peut être simulé au prix de davantage de cycles de processeur 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 jamais vu une jointure CROSS utilisée est lors des tests de charge des serveurs de base de données.

Voyons comment fonctionnent les jointures de base, 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.

Le numéro automatique est un entier long incrémenté automatiquement, affecté aux entrées au fur et à mesure de leur ajout à la table. L'option Texte n'a pas été modifiée, de sorte qu'elle acceptera une chaîne de texte d'une longueur maximale de 255 caractères..

Maintenant, remplissez-les avec des données.

Pour montrer les différences de 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 allant à Créer> Création de requête. Sélectionnez les deux tables dans la boîte de dialogue Afficher la table et cliquez sur Ajouter, puis 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 tableaux 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 allons d'abord regarder la jointure INNER, alors cliquez sur OK pour fermer la boîte de dialogue..

Dans le concepteur de requêtes, sélectionnez les champs que vous voulez 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 est affiché avec la valeur de la table ProdA dans la première colonne et ProdB dans la seconde..

Notez que les résultats affichent uniquement les 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 ne figure pas dans les résultats, car 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 Afficher du ruban et en basculant vers Vue SQL, 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, définissez le type de jointure sur 2 (LEFT OUTER). Exécutez la requête pour voir les résultats.

Comme vous pouvez le constater, chaque entrée de la table ProdA est représentée dans les résultats alors que seules celles de ProdB ayant une entrée correspondante ProductID 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'y a pas de 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 ce qui se trouve dans la table ProdB alors qu'elle affiche des valeurs vides (appelées NULL) lorsque la table ProdA n'a pas de valeur correspondante. Jusqu'ici, 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;

Utiliser des fonctions dans une requête

Les résultats d'une fonction peuvent également être renvoyés dans le cadre d'une requête. Nous souhaitons 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édiat (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 vraie et le troisième paramètre est la valeur à renvoyer si la condition est fausse.

La construction de fonction complète pour notre situation ressemble à ceci:

IIF (ProdA.ProductID est Null, ProdB.ProductName, ProdA.ProductName)

Notez que le paramètre condition ne vérifie pas l'égalité. Une valeur Null dans une base de données n'a pas de valeur pouvant être comparée à une autre valeur, y compris une autre Null. En d'autres termes, Null n'est pas égal à Null. Déjà. Pour dépasser cela, nous vérifions plutôt la valeur en utilisant le mot clé 'Is'.

Nous aurions pu également utiliser 'Is Not Null' et modifier l'ordre des paramètres True et False pour obtenir le même résultat..

Lorsque vous placez cela dans le Concepteur de requêtes, vous devez taper la fonction entière dans l'entrée Field:. Pour le faire créer la colonne 'Résultats', vous devez utiliser un alias. Pour ce faire, faites précéder la fonction par «Résultats», comme indiqué dans la capture d'écran suivante..

Le code SQL équivalent pour faire cela serait:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID est Null, ProdB.ProductName, ProdA.ProductName) AS Résultats de ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Maintenant, quand nous lancerons cette requête, elle produira ces résultats.

Nous voyons ici 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 Résultats, ce qui correspond 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 gratuites en ligne Comment apprendre à Microsoft Access: 5 Ressources gratuites en ligne Comment apprendre à utiliser Microsoft Access: 5 Ressources gratuites en ligne Faut-il gérer beaucoup de données? Vous devriez regarder dans Microsoft Access. Nos ressources d'étude gratuites peuvent vous aider à démarrer et à acquérir les compétences nécessaires pour des projets plus complexes. Lire la suite .