Microsoft Access ist wohl das leistungsstärkste Werkzeug in der gesamten Microsoft Office-Suite, aber es macht Office-Power-User mystifiziert (und manchmal erschreckt). Mit einer steileren Lernkurve als Word oder Excel, wie sollte sich jemand den Umgang mit diesem Werkzeug zuwenden? In dieser Woche wird Bruce Epper einige der Probleme, die durch diese Frage ausgelöst werden, von einem unserer Leser untersuchen.
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 enthalten.
Ich möchte herausfinden, welche Produkte aus Tabelle A in Tabelle B zu finden sind. Ich möchte eine Spalte mit dem Namen Ergebnisse hinzufügen, die den Produktnamen aus Tabelle A enthält, falls vorhanden, und den Produktnamen aus Tabelle B, wenn dieser 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 nutzt Microsoft Jet-Datenbank-Engine für die Datenverarbeitung und -speicherung. Es bietet auch eine grafische Benutzeroberfläche für Benutzer, die es fast überflüssig macht, Structured Query Language (SQL) zu verstehen.
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 mit Access oder einem anderen RDBMS noch nicht vertraut sind, würde ich vorschlagen, dass Sie mit diesen Ressourcen beginnen, bevor Sie fortfahren:
- Was ist eine Datenbank? Also, was ist eine Datenbank? [MakeUseOf erklärt] Also, was ist eine Datenbank? [MakeUseOf Explains] Für einen Programmierer oder Technologie-Enthusiasten ist das Konzept einer Datenbank eine Selbstverständlichkeit. Für viele Menschen ist das Konzept einer Datenbank jedoch ein bisschen fremd. Lesen Sie mehr wo Ryan Dube Excel verwendet, um die Grundlagen relationaler Datenbanken zu zeigen.
- Eine Kurzanleitung zum Einstieg in Microsoft Access 2007 Eine Kurzanleitung zum Einstieg in Microsoft Access 2007 Eine Kurzanleitung zum Einstieg in Microsoft Access 2007 Lesen Sie mehr. Dies ist eine allgemeine Übersicht über Access und die Komponenten einer Access-Datenbank.
- Ein kurzes Lernprogramm für Tabellen in Microsoft Access 2007 Ein schnelles Lernprogramm für Tabellen in Microsoft Access 2007 Ein schnelles Lernprogramm für Tabellen in Microsoft Access 2007 Lesen Sie mehr über das Erstellen Ihrer ersten Datenbank und Tabellen zum Speichern Ihrer strukturierten Daten.
- Eine kurze Einführung zu Abfragen in Microsoft Access 2007 Eine schnelle Einführung zu Abfragen in Microsoft Access 2007 Eine kurze Einführung zu Abfragen In Microsoft Access 2007 lesen Sie mehr über die Möglichkeiten, bestimmte Teile der in den Datenbanktabellen gespeicherten Daten zurückzugeben.
Ein grundlegendes Verständnis der Konzepte, die in diesen Artikeln bereitgestellt werden, wird das folgende etwas leichter verdauen lassen.
Datenbankbeziehungen und Normalisierung
Stellen Sie sich vor, Sie betreiben eine Firma, die 50 verschiedene Arten von Widgets auf der ganzen Welt verkauft. 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 - effektiv eine einzelne Datenbanktabelle. Und jedes Jahr fügt Tausende von Zeilen zu Ihrer Tabelle hinzu.
Die oben genannten Bilder sind Teil der Order-Tracking-Tabelle, die Sie verwenden. Nun sagen Sie, dass beide Kunden mehrmals im Jahr Widgets von Ihnen kaufen, so dass Sie für beide 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 ist komplizierter, wenn Sie zwei verschiedene Kunden mit dem Namen "Joan Smith" haben. Es ist jetzt 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 wir Artikel in mehrere Tabellen wie Inventar, Kunden und Bestellungen aufteilen.
Wenn wir uns nur den Client-Teil 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. Im Bild oben habe ich auch die Dinge für einen feineren Zugriff auf die Daten besser durchbrochen. Die neue Tabelle enthält auch eine Spalte für einen Primärschlüssel (ClientID) - eine Nummer, die für den Zugriff auf jede Zeile in dieser Tabelle verwendet wird.
In der ursprünglichen Tabelle, in der wir diese Daten entfernt haben, würden wir eine Spalte für einen Fremdschlüssel (ClientID) hinzufügen, die mit der richtigen Zeile verknüpft ist, die die Informationen für diesen speziellen Client enthält.
Jetzt, wenn Joan Smith ihren Namen in Joan Baines ändert, muss die Änderung nur einmal in der Client-Tabelle vorgenommen werden. Jede andere Referenz aus verbundenen Tabellen zieht den richtigen Kundennamen und ein Bericht, der sich anschaut, was Joan in den letzten 5 Jahren gekauft hat, erhält alle Bestellungen sowohl unter ihren ersten als auch ihren verheirateten Namen, ohne zu ändern, wie der Bericht generiert wird .
Als zusätzlichen Vorteil reduziert dies auch die Gesamtmenge an Speicherverbrauch.
Arten verbinden
SQL definiert fünf verschiedene Arten von Joins: INNER, LINKS OUTER, RECHTEN OUTER, FULL OUTER und CROSS. Das OUTER-Schlüsselwort ist in der SQL-Anweisung optional.
Microsoft Access ermöglicht die Verwendung von INNER (Standard), LINKEN ÄUSSEREN, RECHTEN ÄUSSEREN und KREUZ. FULL OUTER wird nicht als solches unterstützt, aber mit LEFT OUTER, UNION ALL und RIGHT OUTER kann es auf Kosten von mehr CPU-Zyklen und I / O-Operationen gefälscht werden.
Die Ausgabe eines CROSS-Joins enthält jede Zeile der linken Tabelle, die mit jeder Zeile der rechten Tabelle gepaart ist. Das einzige Mal, dass ich jemals einen CROSS Join gesehen habe, ist der Belastungstest von Datenbankservern.
Lassen Sie uns einen Blick darauf werfen, wie die grundlegenden Joins funktionieren, und dann werden wir sie an unsere Bedürfnisse anpassen.
Beginnen wir mit der Erstellung 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 Option Text wurde nicht geändert, sodass eine Textzeichenfolge mit maximal 255 Zeichen akzeptiert wird.
Füllen Sie sie nun mit Daten aus.
Um die Unterschiede in der Funktionsweise der 3 Join-Typen aufzuzeigen, habe ich die Einträge 1, 5 und 8 aus ProdA gelöscht.
Erstellen Sie als Nächstes eine neue Abfrage, indem Sie auf Erstellen> Query Design gehen . Wählen Sie beide Tabellen im Dialogfeld Tabelle anzeigen aus und klicken Sie auf Hinzufügen und dann auf Schließen .
Klicken Sie in der Tabelle ProdA auf ProductID, ziehen Sie diese in ProductID in die Tabelle ProdB und lassen Sie die Maustaste los, um die Beziehung zwischen den Tabellen zu erstellen.
Klicken Sie mit der rechten Maustaste auf die Linie zwischen den Tabellen, die die Beziehung zwischen den Elementen darstellen, und wählen Sie Eigenschaften verbinden .
Standardmäßig ist Join-Typ 1 (INNER) ausgewählt. Option 2 ist ein LEFT OUTER Join und 3 ist ein RIGHT OUTER Join.
Wir werden zuerst den INNER-Join betrachten, klicken Sie also auf OK, um den Dialog zu schließen.
Wählen Sie im Abfrage-Designer die Felder aus, die Sie in den Dropdown-Listen sehen möchten.
Wenn wir die Abfrage ausführen (das rote Ausrufezeichen in der Multifunktionsleiste), wird das Feld ProductName aus beiden Tabellen 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 anzeigen, bei denen ProductID in beiden Tabellen gleich ist. Obwohl in der Tabelle ProdB ein Eintrag für ProductID = 1 vorhanden ist, wird er in den Ergebnissen nicht angezeigt, da ProductID = 1 in der Tabelle ProdA nicht vorhanden ist. Das gleiche gilt für ProductID = 11. Es existiert in der Tabelle ProdA, aber nicht in der Tabelle ProdB.
Wenn Sie die Schaltfläche Ansicht in der Multifunktionsleiste verwenden und zur SQL-Ansicht wechseln, können Sie die SQL-Abfrage sehen, die vom Designer zum Abrufen dieser Ergebnisse generiert wurde.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Kehren Sie zur Entwurfsansicht zurück und ändern Sie den Join-Typ auf 2 (LEFT OUTER). Führen Sie die Abfrage aus, um die Ergebnisse anzuzeigen.
Wie Sie sehen können, wird jeder Eintrag in der Tabelle ProdA in den Ergebnissen dargestellt, während in den Ergebnissen nur die Einträge in ProdB angezeigt werden, die einen passenden ProductID-Eintrag in der Tabelle ProdB haben.
Das Leerzeichen in der Spalte ProdB.ProductName ist ein spezieller Wert (NULL), da in Tabelle ProdB kein übereinstimmender Wert vorhanden ist. Dies wird sich später als wichtig erweisen.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Versuchen Sie dasselbe mit der dritten Verbindungsart (RIGHT OUTER).
Die Ergebnisse zeigen alles aus der Tabelle ProdB, während es leere Werte (NULL-Werte) anzeigt, für die die ProdA-Tabelle keinen übereinstimmenden Wert hat. Bis jetzt bringt uns das den Ergebnissen, die in der Leserfrage erwünscht sind, am nächsten.
SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; Funktionen in einer Abfrage verwenden
Die Ergebnisse einer Funktion können auch als Teil einer Abfrage zurückgegeben werden. Wir möchten, dass in unserer Ergebnismenge eine neue Spalte mit dem Namen "Ergebnisse" erscheint. Sein Wert ist der Inhalt der ProductName-Spalte der Tabelle ProdA, wenn ProdA einen Wert hat (es ist nicht NULL), andernfalls sollte es aus der Tabelle ProdB genommen werden.
Die Immediate IF (IIF) -Funktion kann verwendet werden, um dieses Ergebnis zu erzeugen. Die Funktion benötigt drei Parameter. Die erste ist eine Bedingung, die zu einem Wahr- oder Falsch-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 so aus:
IIF(ProdA.ProductID Is 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 verglichen werden kann, einschließlich einer anderen Null. Mit anderen Worten, Null ist nicht gleich Null. Je. Um dies zu erreichen, ü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 diese in den Abfrage-Designer einfügen, müssen Sie die gesamte Funktion in den Feldeintrag eingeben. Um die Spalte 'Ergebnisse' zu erstellen, müssen Sie einen Alias verwenden. Richten Sie dazu die Funktion mit 'Results:' wie im folgenden Screenshot gezeigt ein.
Der entsprechende SQL-Code hierfür wäre:
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; Jetzt, wenn wir diese Abfrage ausführen, werden diese Ergebnisse erzeugt.
Hier sehen wir für jeden Eintrag, wo die Tabelle ProdA einen Wert hat, dieser Wert wird in der Spalte Ergebnisse wiedergegeben. Wenn es keinen Eintrag in der ProdA-Tabelle gibt, erscheint der Eintrag von ProdB in Results, was genau das ist, was unser Leser gefragt hat.
Weitere Ressourcen zum Erlernen von Microsoft Access finden Sie in Joel Lees Lernprozedur für Microsoft Access: 5 kostenlose Online-Ressourcen So lernen Sie Microsoft Access: 5 kostenlose Online-Ressourcen So erlernen Sie Microsoft Access: 5 kostenlose Online-Ressourcen Sie müssen eine große Menge verwalten von Dateien? Sie sollten in Microsoft Access schauen. Unsere kostenlosen Lernressourcen können Ihnen den Einstieg erleichtern und die Fähigkeiten für komplexere Projekte erlernen. Weiterlesen .