Eine einfache Suchfunktion ist schnell gebaut. Ein Suchfeld einrichten, eine Schaltfläche mit dem notwendigen VBA-Code anlegen und schließlich ein Steuerelement zum Anzeigen des Suchergebnisses. Was aber, wenn es nicht nur eines, sondern viele Kriterien gibt, und diese sich auch noch über mehrere Tabellen erstrecken Dieser Beitrag diskutiert die Möglichkeiten und zeigt einen Ansatz, mit dem Sie auf jeden Fall den Überblick behalten – egal, wie viele Kriterien und Tabellen die Suche umfasst.
Wer Daten in einer Datenbank speichert, will diese meist auch nach verschiedenen Kriterien durchsuchen. Oftmals reichen wenige Suchmerkmale: Wer beispielsweise einen Shop betreibt, braucht in der Regel nur die Kundennummer, um dann zu einer angefragten Bestellung zu gelangen. Dies erledigt ein kleines Suchfeld zur Eingabe eben dieser Nummer. Die Abfrage, die aus den Kundendatensätzen den passenden heraussucht, ist ebenso einfach und die größte Herausforderung dürfte schon sein, sich für ein Listenfeld oder ein Unterformular zur Anzeige der gefundenen Kundendaten zu entscheiden.
Viele Felder
Etwas umfangreicher wird es, wenn sich der Suchbegriff nicht nur auf eines, sondern auf mehrere Tabellenfelder bezieht. Dies ändert im Vergleich zum ersten Beispiel nicht viel; Sie müssen lediglich die Abfrage um einige Kriterien erweitern.
Tabelle mit Lookup-Feldern
Lookup-Felder zeigen in Lookup-Tabellen ausgelagerte Informationen an. Einfachstes Beispiel ist die Anrede, die oft in der eigentlichen Tabelle als Fremdschlüsselfeld angelegt ist und auf eine weitere Tabelle namens tblAnreden verweist, in der die eigentlichen Begriffe wie Herr oder Frau stehen.
Das Durchsuchen von Tabellen mit Lookup-Feldern ist ebenfalls kein Hexenwerk: Man verwendet dann einfach eine Abfrage, welche die benötigten Felder der Lookup-Tabellen einbindet, und legt die Kriterien für diese Felder genau wie für die übrigen Felder dieser Tabelle fest.
Viele Tabellen
Richtig interessant wird es, wenn eine Tabelle viele verknüpfte Tabellen besitzt, die je nach Art der Information per 1:n- oder auch per m:n-Beziehung angebunden sind. Ein passendes Beispiel ist eine Dokumentenverwaltung.
Diese besteht nicht nur aus der Tabelle mit den Dokumenten selbst, sondern aus vielen weiteren Informationen, die sich teils in per 1:n-Beziehung verknüpften Tabellen (zum Beispiel Dokumentart oder Versionen) oder auch in Tabellen befinden, die durch eine m:n-Beziehung angebunden sind (Autoren, Kategorien, Stichwörter). Mitunter befinden sich die interessanten Felder dort sogar in den Verknüpfungstabellen zur Realisierung der m:n-Beziehung. Das Datenmodell des Beispiels können Sie Bild 1 entnehmen.
Bild 1: Datenmodell der Beispieldatenbank
Eine große Abfrage
Prinzipiell könnte man eine große Abfrage erstellen, die alle potenziell für die Suche relevanten Tabellen enthält und dabei die im Datenmodell festgelegten INNER JOINs, also die Verknüpfungen zwischen den Tabellen, beibehält. Diese würde man dann in Abhängigkeit von den im Suchfenster gewählten Kriterien mit einer entsprechenden WHERE-Klausel ausstatten. Grundsätzlich ist das die einfachste Methode. Allerdings muss man mehrere Faktoren berücksichtigen:
- Je mehr Tabellen die Abfrage enthält, desto mehr JOINs sind vorhanden. Dies bedeutet eine entsprechend geringe Performance.
- Wenn sie die INNER JOINs vom Datenmodell übernehmen und tatsächlich alle Tabellen in die Abfrage integrieren, müssen Sie sich bewusst sein, dass auch für alle verknüpften Tabellen Daten zu einem Datensatz der Haupttabelle vorhanden sein muss.
- Ist das nicht der Fall, also wenn es wie im hier vorgestellten Beispiel auch Elemente in der Haupttabelle (tblDokumente) gibt, für die mindestens eine verknüpfte Tabelle (zum Beispiel tblDokumenteStichwoerter) keine Daten enthält, dann werden diese Dokumente im Abfrageergebnis auch nicht angezeigt.
Dies können Sie am Beispiel der Abfrage aus Bild 2 prüfen. Wenn die Tabelle tblDokumente zwei Datensätze enthält, von denen lediglich einer mit Einträgen der Tabelle tblDokumenteAttribute verknüpft ist, dann liefert die Abfrage auch nur den oder die verknüpften Datensätze zurück.
Bild 2: Beispielabfrage mit per m:n-Beziehung verknüpften Tabellen
Prinzipiell ist ein INNER JOIN also auch ein Kriterium, auch wenn man es nicht an der Stelle festlegt, an der man üblicherweise die Kriterien einträgt, also in der Zeile Kriterien des Abfrageentwurfs.
Wenn man dies auf eine große Abfrage überträgt, die alle verknüpften Tabellen enthält, müsste ein Dokument zunächst einmal Einträge in allen verknüpften Tabellen aufweisen, um überhaupt im Ergebnis einer solchen Abfrage aufzutauchen. Sobald es entweder keine Stichwörter (tblDokumenteStichwoerter), Kategorien (tblDokumenteKategorien), Autoren (tblDokumenteAutoren) oder sonstige Attribute aufweist (tblDokumenteAttribute), zeigt die Abfrage ein Dokument auch nicht an – unabhängig von weiteren hinzugefügten Kriterien.
LEFT JOIN statt INNER JOIN
Wie nun kann man eine Abfrage mit verknüpften Tabellen verwenden, ohne dass nicht vorhandene Daten in einer der verknüpften Tabellen direkt das K.O.-Kriterium für einen Dokumentdatensatz bedeuten Probieren wir es aus und stellen schnell eine passende Abfrage zusammen, indem wir die fünf Tabellen tblDokumente, tblAutoren, tblKategorien, tblDokumenteAutoren sowie tblDokumenteKategorien in den Abfrageentwurf ziehen und die Felder ID, Pfad und Dateiname der Tabelle tblDokumente sowie Kategorie aus tblKategorien und NameAutor aus tblAutoren im Raster der anzuzeigenden Felder unterbringen (siehe Bild 3).
Bild 3: Entwurfsansicht einer Abfrage mit einer Tabelle, die mit zwei anderen Tabellen per m:n-Beziehung verknüpft ist
Ein schneller Wechsel in die Datenblattansicht sollte zumindest einen Datensatz anzeigen, denn die Datenbank enthält bereits einige Datensätze, von denen zumindest einer mit allen per m:n-Beziehung verknüpften Tabellen verbunden ist. Aber weit gefehlt: Dort erscheint kein einziger Datensatz. Das liegt daran, dass Access beim Anlegen der Beziehungen im Abfrageentwurf ein wenig zu eifrig war: Es hat nämlich Beziehungen zwischen den Feldern ID der Tabellen tblDokumente und tblAutoren, tblDokumente und tblKategorien sowie tblDokumenteAutoren und tblDokumenteKategorien angelegt. Schuld war die Option Autoverknüpfung aktivieren, die Sie aber ausschalten können. Ohne diese ist der Abfrageentwurf gleich ein wenig überschaubarer, wie Bild 4 zeigt, und außerdem blendet das Ergebnis auch einige Datensätze ein (siehe Bild 5).
Bild 4: Ohne zusätzliche Beziehungen klappt es auch mit den Verknüpfungen.
Bild 5: Ohne zusätzliche Beziehungen klappt es besser mit den Verknüpfungen.
Betrachten wie diese Abfrage nun stellvertretend für eine viel umfangreichere Abfrage, die alle Tabellen des Beispieldatenmodells enthält. Das Ziel wäre nun, die Daten mit dieser Abfrage nach nur einem Kriterium wie etwa dem Autor zu durchsuchen und alle passenden Dokumente zu ermitteln, auch wenn diese nicht mit einem Datensatz der Tabelle tblKategorien verknüpft sind.
Ein Ansatz wäre, statt eines INNER JOIN ein OUTER JOIN zu verwenden, das dafür sorgt, dass die Abfrage nicht nur die Datensätze ermittelt, die über alle enthaltenen Tabellen verteilt sind, sondern für alle Datensätze der Tabelle tblDokumente mindestens einen Eintrag ausgibt, auch wenn diese nicht mit der Autoren- und der Kategorientabelle verknüpft sind.
Fangen wir einfach an und versuchen die INNER JOIN-Methode zunächst nur mit den Tabellen tblDokumente, tblDokumenteAutoren und tblDokumenteKategorien (siehe Bild 6). Dies funktioniert ohne Probleme: Die Abfrage zeigt etwas mehr als die in der Tabelle tblDokumente enthaltenen Datensätze an, was daraus resultiert, dass einige Dokumente mit mehreren Einträgen der Tabellen tblDokumenteAutoren und tblDokumenteKategorien verknüpft sind.
Bild 6: Die Tabelle tblDokumente ist über zwei OUTER JOINs mit den Tabellen tblDokumenteKategorien und tblAutoren verknüpft.
Jetzt wird es interessant: Wir fügen die Tabellen tblAutoren und tblKategorien hinzu, denn immerhin sollen die Felder NameAutor und Kategorie gegebenenfalls als Suchkriterien zum Einsatz kommen. Doch der Versuch scheitert: Die Abfrage enthält mehrdeutige Inklusionsverknüpfungen, wie Bild 7 verrät.
Bild 7: OUTER JOINs mit anhängenden Tabellen vertragen sich nicht.
Die Meldung weist außerdem darauf hin, dass man einige der Verknüpfungen auf einzelne Abfragen verteilt und diese dann zusammenführt. Das funktioniert schließlich: Sie erstellen dazu zwei Abfragen mit dem folgenden SQL-Ausdruck, die jeweils das benötigte Feld NameAutor (tblAutoren) beziehungsweise Kategorie (tblKategorien) und das Feld DokumentID der jeweiligen Verknüpfungstabelle tblDokumenteAutoren und tblDokumenteKategorien enthalten und unter den Namen qryDokumentAutoren und qryDokumenteKategorien gespeichert wurden:
SELECT tblAutoren.NameAutor, tblDokumenteAutoren.DokumentID FROM tblAutoren INNER JOIN tblDokumenteAutoren ON tblAutoren.ID = tblDokumenteAutoren.AutorID; SELECT tblKategorien.Kategorie, tblDokumenteKategorien.DokumentID FROM tblKategorien INNER JOIN tblDokumenteKategorien ON tblKategorien.ID = tblDokumenteKategorien.KategorieID;
Diese beiden ergeben dann in einer weiteren Abfrage zusammen mit der Tabelle tblDokumente ein Konstrukt, das die Ausgabe von OUTER JOINs mit anhängenden Tabellen erlaubt (siehe Bild 8).
Bild 8: Wenn man Beziehungen mit weiteren Tabellen in Abfragen versteckt, funktionieren OUTER JOINs.
Minimale Abfragen
Es wäre also insgesamt möglich, eine große Abfrage zu erstellen, die alle benötigten Elemente enthält und für die man in Abhängigkeit von den gewünschten Kriterien eine passende WHERE-Klausel zusammensetzt.
Wenn man davon ausgeht, dass die Abfrage im einfachsten Fall nur die IDs der gefundenen Dokumente zurückliefern soll, hängt an dieser Abfrage jedoch ein ziemlicher Overhead. Immerhin schleppt die Abfrage alle damit verknüpften Tabellen mit, auch wenn der Benutzer möglicherweise nur Dokumente mit einem bestimmten Namen aufgelistet bekommen möchte. Der notwendige SQL-Ausdruck der Abfrage sieht so wie in Bild 9 aus, und dieser spart schon die wegen der mehrdeutigen Inklusionsverknüpfung in weiteren Abfragen enthaltenen Tabellen ein. Wenn jemand nur nach einem bestimmten Dokumenttitel sucht, würde theoretisch ja auch die folgende Abfrage ausreichen:
Bild 9: Dieser SQL-Ausdruck liefert alle möglichen Suchfelder der Tabellen des Beispieldatenmodells.
SELECT ID FROM tblDokumente WHERE Dateiname Like 'Access*';
Wenn die gefundenen Datensätze beispielsweise in einem Listenfeld angezeigt werden sollen, welches die wichtigsten Informationen der Tabelle tblDokumente anzeigt, müssten Sie die Tabelle tblDokumente lediglich noch mit der obigen Abfrage zusammenführen, statt das Gebilde aus Bild 9 zu verwenden.
Dynamik ist Trumpf
Aber ist es möglich, immer nur die Tabellen in eine Abfrage zu integrieren, für die der Benutzer in einer Suche auch Kriterien angegeben hat Die Antwort lautet: Ja. Allerdings sind dafür einige Zeilen VBA-Programmierung nötig. Es gibt zwei Möglichkeiten:
- Sie verwenden eine VBA-Routine, die eine den Kriterien des Benutzers entsprechende Abfrage zusammensetzt. Diese Aufgabe ist mit wachsender Anzahl von Tabellen nur noch schwer zu handhaben.
- Sie setzen das Abfrageergebnis in kleinen Schritten zusammen.
Letzteres schauen wir uns in den folgenden Abschnitten im Detail an.
Schritt-für-Schritt-Abfragen
Bei der Schritt-für-Schritt-Abfrage beginnen Sie zunächst mit der Basistabelle, hier tblDokumente, und werten die Kriterien aus, die sich auf Felder dieser Tabelle beziehen. Daraus resultiert eine Abfrage und ein entsprechendes Ergebnis, das Sie in einer temporären Tabelle namens tblTempSuche mit den beiden Feldern ID (Autowert) und DokumentID (Zahl) zwischenspeichern. Als Nächstes untersuchen Sie die Kriterien, die sich auf Lookup-Tabellen beziehen wie die Dokumentart (tblDokumentarten) oder Status (tblStatus). Für jede Tabelle erzeugen Sie per VBA-Code wiederum ein SQL-Statement, das die Datensätze des gespeicherten Zwischenergebnisses mit dem neuen Kriterium abgleicht.
Anschließend geht es an die Tabellen, die per m:n-Beziehung mit der Tabelle tblDokumente verknüpft sind. Diese werden nach dem gleichen Schema abgearbeitet: Das aktuelle Suchzwischenergebnis wird mit dem gegebenenfalls vorhandenen Kriterium in diesen Tabellen abgeglichen und wiederum gespeichert.
Suchformular
Bevor wir uns den Details dieser Vorgehensweise zuwenden, bauen wir ein kleines Suchformular, das in den nächsten Abschnitten noch wachsen wird. Zu Beginn enthält es lediglich ein Textfeld zur Eingabe eines Dateinamens, eine Schaltfläche zum Durchführen der Suche und ein Unterformular, das die gefundenen Suchergebnisse anzeigt.
Das Unterformular sfmSuche soll einige Felder der Tabelle tblDokumente in der Datenblattansicht anzeigen (siehe Bild 10). Allerdings nur die, die auch mit den in der Tabelle tblTempSuche gespeicherten Daten übereinstimmen, und diese liefert die Abfrage qrySuchergebnis (siehe Bild 11).
Bild 10: Das Unterformular zeigt die Suchergebnisse in der Datenblattansicht an.
Bild 12: Das Hauptformular der Suchfunktion in der Minimalausführung
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo