Fragen Sie die Experten

So schreiben Sie Microsoft Access SQL-Abfragen von Grund auf

So schreiben Sie Microsoft Access SQL-Abfragen von Grund auf / Fragen Sie die Experten

Microsoft Access ist zweifellos das leistungsfähigste Tool in der gesamten Microsoft Office-Suite. Dennoch macht es Office-Power-Benutzer vor Rätsel. Mit einer steileren Lernkurve als mit Word oder Excel, wie sollte sich jeder mit der Verwendung dieses Tools beschäftigen? In dieser Woche wird sich Bruce Epper mit einem Teil unserer Leser mit einigen Fragen befassen.

Ein Leser fragt:

Ich habe Probleme beim Schreiben einer Abfrage in Microsoft Access.

Ich habe eine Datenbank mit zwei Produkttabellen, die eine gemeinsame Spalte mit einem numerischen Produktcode und einem zugehörigen Produktnamen enthält.

Ich möchte herausfinden, welche Produkte aus Tabelle A in Tabelle B zu finden sind. Ich möchte eine Spalte mit dem Namen Results hinzufügen, die den Produktnamen aus Tabelle A (falls vorhanden) und den Produktnamen aus Tabelle B enthält, wenn er nicht existiert in Tabelle A.

Hast du irgendeinen Ratschlag?

Bruce's Antwort:

Microsoft Access ist ein Datenbankverwaltungssystem (DBMS), das für die Verwendung auf Windows- und Mac-Computern entwickelt wurde. Es verwendet die Jet-Datenbank-Engine von Microsoft zur Datenverarbeitung und -speicherung. Es bietet auch eine grafische Benutzeroberfläche, die das Verständnis der SQL-Sprache (Structured Query Language) nahezu überflüssig macht..

SQL ist die Befehlssprache, die zum Hinzufügen, Löschen, Aktualisieren und Zurückgeben von in der Datenbank gespeicherten Informationen sowie zum Ändern von Kerndatenbankkomponenten wie Hinzufügen, Löschen oder Ändern von Tabellen oder Indizes verwendet wird.

Startpunkt

Wenn Sie noch nicht mit Access oder einem anderen RDBMS vertraut sind, sollten Sie zunächst mit diesen Ressourcen beginnen, bevor Sie fortfahren:

  • Was ist eine Datenbank? Was ist eine Datenbank überhaupt? [MakeUseOf erklärt] Also, was ist eine Datenbank überhaupt? [MakeUseOf Explains] Für einen Programmierer oder einen Technologie-Enthusiasten ist das Konzept einer Datenbank wirklich selbstverständlich. Für viele Leute ist das Konzept einer Datenbank jedoch etwas fremd… Lesen Sie mehr, wo Ryan Dube Excel verwendet, um die Grundlagen relationaler Datenbanken zu veranschaulichen.
  • Eine Kurzanleitung Erste Schritte mit Microsoft Access 2007 Eine Kurzanleitung Erste Schritte mit Microsoft Access 2007 Eine Kurzanleitung Erste Schritte mit Microsoft Access 2007 Read More ist eine allgemeine Übersicht über Access und die Komponenten, aus denen eine Access-Datenbank besteht.
  • Eine kurze Einführung in Tabellen in Microsoft Access 2007 Eine kurze Einführung in Tabellen in Microsoft Access 2007 Eine kurze Einführung in Tabellen in Microsoft Access 2007 Lesen Sie mehr über das Erstellen der ersten Datenbank und von Tabellen zum Speichern strukturierter Daten.
  • Eine kurze Einführung in Abfragen in Microsoft Access 2007 Eine kurze Einführung in Abfragen in Microsoft Access 2007 Eine kurze Einführung in Abfragen in Microsoft Access 2007 Read More befasst sich mit der Möglichkeit, bestimmte Teile der in den Datenbanktabellen gespeicherten Daten zurückzugeben.

Wenn Sie ein grundlegendes Verständnis für die Konzepte in diesen Artikeln haben, wird das folgende etwas leichter zu verstehen.

Datenbankbeziehungen und Normalisierung

Stellen Sie sich vor, Sie betreiben ein Unternehmen, das weltweit 50 verschiedene Arten von Widgets anbietet. Sie haben einen Kundenstamm von 1.250 und verkaufen in einem durchschnittlichen Monat 10.000 Widgets an diese Kunden. Sie verwenden derzeit eine einzige Tabelle, um alle diese Verkäufe zu verfolgen - und zwar effektiv eine einzelne Datenbanktabelle. Jedes Jahr fügt Ihre Tabelle Tausende von Zeilen hinzu.

Die obigen Bilder sind Teil der von Ihnen verwendeten Order Tracking-Tabelle. Nehmen wir an, beide Kunden kaufen Widgets mehrmals im Jahr von Ihnen, sodass Sie für beide weitaus mehr Zeilen haben.

Wenn Joan Smith Ted Baines heiratet und seinen Nachnamen nimmt, muss nun jede einzelne Zeile, die ihren Namen enthält, geändert werden. Das Problem wird verschlimmert, wenn Sie zufällig zwei verschiedene Kunden mit dem Namen 'Joan Smith' haben. Es ist gerade viel schwieriger geworden, Ihre Verkaufsdaten aufgrund eines ziemlich häufigen Ereignisses konsistent zu halten.

Durch die Verwendung einer Datenbank und die Normalisierung der Daten können Elemente in mehrere Tabellen aufgeteilt werden, z. B. Bestandsverzeichnis, Kunden und Aufträge.

Wenn Sie sich den Client-Abschnitt unseres Beispiels ansehen, entfernen wir die Spalten für den Client-Namen und die Client-Adresse und fügen sie in eine neue Tabelle ein. In der Abbildung oben habe ich auch die Dinge für einen detaillierteren Zugriff auf die Daten besser herausgebrochen. Die neue Tabelle enthält auch eine Spalte für einen Primärschlüssel (ClientID) - eine Nummer, mit der auf jede Zeile in dieser Tabelle zugegriffen wird.

In der ursprünglichen Tabelle, in der wir diese Daten entfernt haben, würden wir eine Spalte für eine Fremdschlüssel (ClientID) hinzufügen, die mit der richtigen Zeile verknüpft ist, die die Informationen für diesen bestimmten Client enthält.

Nun, wenn Joan Smith ihren Namen in Joan Baines ändert, muss die Änderung nur einmal in der Client-Tabelle vorgenommen werden. Jede zweite Referenz aus verbundenen Tabellen wird den richtigen Kundennamen abrufen, und ein Bericht, der betrachtet, was Joan in den letzten fünf Jahren gekauft hat, erhält alle Bestellungen unter ihrem Mädchennamen und ihrem verheirateten Namen, ohne die Erstellung des Berichts ändern zu müssen.

Als zusätzlicher Vorteil reduziert dies auch den gesamten Speicherbedarf.

Join-Typen

SQL definiert fünf verschiedene Arten von Joins: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER und CROSS. Das Schlüsselwort OUTER ist in der SQL-Anweisung optional.

Microsoft Access ermöglicht die Verwendung von INNER (Standard), LEFT OUTER, RIGHT OUTER und CROSS. FULL OUTER wird nicht als solches unterstützt. Wenn Sie LEFT OUTER, UNION ALL und RIGHT OUTER verwenden, kann dies jedoch auf Kosten von mehr CPU-Zyklen und E / A-Vorgängen gefälscht werden.

Die Ausgabe eines CROSS-Joins enthält jede Zeile der linken Tabelle, die mit jeder Zeile der rechten Tabelle gekoppelt ist. Das einzige Mal, dass ich einen Cross-Join gesehen habe, ist das Testen von Datenbankservern.

Lassen Sie uns einen Blick darauf werfen, wie die grundlegenden Verknüpfungen funktionieren. Dann werden wir sie an unsere Bedürfnisse anpassen.

Beginnen wir mit dem Erstellen von zwei Tabellen, ProdA und ProdB, mit den folgenden Designeigenschaften.

Die AutoNumber ist eine automatisch inkrementierende lange Ganzzahl, die den Einträgen zugewiesen wird, wenn sie der Tabelle hinzugefügt werden. Die Textoption wurde nicht geändert, daher wird eine Textzeichenfolge mit einer Länge von bis zu 255 Zeichen akzeptiert.

Füllen Sie sie jetzt mit einigen Daten auf.

Um die Unterschiede in der Funktionsweise der 3 Join-Typen aufzuzeigen, habe ich die Einträge 1, 5 und 8 in ProdA gelöscht.

Als nächstes erstellen Sie eine neue Abfrage, indem Sie zu gehen Erstellen> Abfragedesign. Wählen Sie beide Tabellen im Dialogfeld "Tabelle anzeigen" und aus Klicken Sie auf Hinzufügen, dann Schließen.

Klicken Sie auf ProductID in der Tabelle ProdA, ziehen Sie es in die ProductID in Tabelle ProdB und lassen Sie die Maustaste los, um die Beziehung zwischen den Tabellen zu erstellen.

Klicken Sie mit der rechten Maustaste auf die Zeile zwischen den Tabellen, die die Beziehung zwischen den Elementen und darstellt Wählen Sie Eigenschaften verbinden.

Standardmäßig ist der Verbindungstyp 1 (INNER) ausgewählt. Option 2 ist ein LEFT-OUTER-Join und 3 ist ein RECHTER OUTER-Join.

Wir schauen uns zuerst den INNER-Join an. Klicken Sie auf OK, um den Dialog zu schließen.

Wählen Sie im Abfrage-Designer die Felder aus, die in den Dropdown-Listen angezeigt werden sollen.

Wenn wir die Abfrage ausführen (das rote Ausrufezeichen in der Multifunktionsleiste), wird in beiden Tabellen das Feld ProductName mit dem Wert aus der Tabelle ProdA in der ersten Spalte und ProdB in der zweiten Spalte angezeigt.

Beachten Sie, dass die Ergebnisse nur Werte zeigen, bei denen ProductID in beiden Tabellen gleich ist. Obwohl in der Tabelle ProdB ein Eintrag für ProductID = 1 enthalten ist, wird dieser in den Ergebnissen nicht angezeigt, da ProductID = 1 nicht in der Tabelle ProdA vorhanden ist. Gleiches gilt für ProductID = 11. Sie existiert in der Tabelle ProdA, aber nicht in der Tabelle ProdB.

Wenn Sie die Schaltfläche Ansicht auf der Multifunktionsleiste verwenden und zur SQL-Ansicht wechseln, können Sie die vom Designer generierte SQL-Abfrage sehen, mit der diese Ergebnisse abgerufen werden.

SELECT ProdA.ProductName, ProdB.ProductName VON ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Wechseln Sie zurück zur Entwurfsansicht und ändern Sie den Verbindungstyp in 2 (LEFT OUTER). Führen Sie die Abfrage aus, um die Ergebnisse anzuzeigen.

Wie Sie sehen, wird jeder Eintrag in der Tabelle ProdA in den Ergebnissen dargestellt, während nur die Einträge in ProdB, die einen passenden ProductID-Eintrag in der Tabelle ProdB haben, in den Ergebnissen angezeigt werden.

Das Leerzeichen in der Spalte ProdB.ProductName ist ein spezieller Wert (NULL), da in der Tabelle ProdB kein übereinstimmender Wert vorhanden ist. Dies wird sich später als wichtig erweisen.

SELECT ProdA.ProductName, ProdB.ProductName VON ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Versuchen Sie dasselbe mit dem dritten Join-Typ (RIGHT OUTER)..

Die Ergebnisse zeigen alles aus der Tabelle ProdB, während leere Werte (bekannt als NULL) angezeigt werden, bei denen die ProdA-Tabelle keinen übereinstimmenden Wert hat. Dies bringt uns bisher den Ergebnissen nahe, die in der Leserfrage gestellt werden.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Verwenden von Funktionen in einer Abfrage

Die Ergebnisse einer Funktion können auch als Teil einer Abfrage zurückgegeben werden. Wir möchten, dass eine neue Spalte mit dem Namen "Ergebnisse" in unserer Ergebnismenge angezeigt wird. Ihr Wert ist der Inhalt der Spalte ProductName der Tabelle ProdA, wenn ProdA einen Wert hat (es ist nicht NULL). Andernfalls sollte er der Tabelle ProdB entnommen werden.

Die Immediate IF (IIF) -Funktion kann verwendet werden, um dieses Ergebnis zu generieren. Die Funktion hat drei Parameter. Die erste ist eine Bedingung, die zu einem True- oder False-Wert ausgewertet werden muss. Der zweite Parameter ist der Wert, der zurückgegeben werden soll, wenn die Bedingung True ist, und der dritte Parameter ist der Wert, der zurückgegeben werden soll, wenn die Bedingung False ist.

Das vollständige Funktionskonstrukt für unsere Situation sieht folgendermaßen aus:

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

Beachten Sie, dass der Bedingungsparameter nicht auf Gleichheit prüft. Ein Nullwert in einer Datenbank hat keinen Wert, der mit einem anderen Wert, einschließlich einem anderen Nullwert, verglichen werden kann. Mit anderen Worten, Null ist nicht gleich Null. Je. Um dies zu umgehen, überprüfen wir den Wert stattdessen mit dem Schlüsselwort 'Is'.

Wir hätten auch 'Is Not Null' verwenden und die Reihenfolge der True- und False-Parameter ändern können, um das gleiche Ergebnis zu erhalten.

Wenn Sie dies in den Query Designer einfügen, müssen Sie die gesamte Funktion in den Eintrag Field: eingeben. Um die Spalte "Ergebnisse" erstellen zu können, müssen Sie einen Alias ​​verwenden. Um dies zu tun, stellen Sie die Funktion mit 'Results:' voran, wie im folgenden Screenshot dargestellt.

Der entsprechende SQL-Code wäre:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID ist Null, ProdB.ProductName, ProdA.ProductName) AS Ergebnisse von ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Wenn wir diese Abfrage ausführen, werden diese Ergebnisse erzeugt.

Hier sehen wir für jeden Eintrag, bei dem die Tabelle ProdA einen Wert hat, dass sich dieser Wert in der Ergebnisspalte widerspiegelt. Wenn es keinen Eintrag in der ProdA-Tabelle gibt, wird der Eintrag von ProdB in Results angezeigt. Dies ist genau das, was unser Leser gefragt hat.

Weitere Informationen zum Erlernen von Microsoft Access finden Sie unter Joel Lee. So lernen Sie Microsoft Access kennen: 5 Kostenlose Online-Ressourcen So lernen Sie Microsoft Access: 5 Kostenlose Online-Ressourcen So erhalten Sie Informationen zu Microsoft Access: 5 Kostenlose Online-Ressourcen Müssen Sie viel verwalten? von Dateien? Sie sollten sich Microsoft Access ansehen. Unsere kostenlosen Lernressourcen helfen Ihnen beim Einstieg und beim Erlernen der Fähigkeiten für komplexere Projekte. Weiterlesen .