Beispiele für Daten aus m:n-Beziehungen gibt es viele – Verteilerlisten mit Publikationen und Empfängern, Bestellungen und Artikel, Fahrzeuge und Zusatzausstattungen oder Rezepte und Zutaten. Datensätze der einen Seite zu suchen, wobei die Bedingung sich auf die Datensätze der anderen Seite beziehen, ist eine gängige Aufgabe.
Im Rahmen dieses Beitrags sollen Personen und deren Skills als Beispiel dienen. Ziel ist es, alle Personen aus der Gesamtmenge herauszufiltern, die bestimmte Skills besitzen. Der Einfachheit halber verzichtet das Beispiel auf eine Bewertung der Skills.
Das Datenmodell der Beispieldatenbank sieht wie in Bild 1 aus. Die beiden zu verknüpfenden Tabellen tblPersonen und tblSkills enthaltenen jeweils nur ein Feld zur näheren Beschreibung. Die Verknüpfungstabelle tblPersonenSkills verknüpft die beiden Tabellen jeweils über eine 1:n-Beziehung.
Geeignete Mitarbeiter gesucht!
Mit einer umfangreichen Datenbank von Personen und deren Skills hat man ein mächtiges Instrument in der Hand, kann man doch nun den Kreis der zukünftigen Mitarbeiter anhand der Skills ordentlich einschränken. Das geht ja ganz einfach: Man erstellt eine Abfrage über die drei beteiligten Tabellen, gibt als Suchkriterien die gewünschten Skills ein und erhält als Ergebnis die am besten geeigneten Mitarbeiter.
Die Abfrage mit dem Entwurf aus Bild 2 zeigt, wie es funktioniert. Das Ergebnis überzeugt: Es wirft alle potenziellen Mitarbeiter aus, die „Access“ als Skill aufweisen.
Ein Mitarbeiter soll aber möglichst nicht nur eine Fähigkeit besitzen – immerhin kann es ja mal sein, dass gerade kein Access-Projekt anliegt.
Dann soll er beispielsweise in einem VB.NET-Projekt eingesetzt werden. Also erweitern Sie die Abfrage, indem Sie als Kriterium statt "Access" nun "Access" und "VB.NET" eintragen. Sie schauen sich das Ergebnis der Abfrage an und – es ist leer! Ist das Zufall? Sind vielleicht gar keine Mitarbeiter in der Datenbank enthalten, die diese speziellen Skills aufweisen? Ein Blick in die Beispieldaten zeigt: Es gibt mindestens eine Person mit den gesuchten Skills. Schnell wird klar: Mit diesem Kriterium kann die Abfrage keinen Datensatz finden, denn es vergleicht ein Feld mit zwei Werten gleichzeitig.
Suchen einer Person mit betimmten Skills
Wie aber kann man die Personen finden, die mit den gesuchten Skills aufwarten? Die Lösung ist nicht ganz trivial und verwendet entgegen allen Erwartungen keine Und-Verknüpfung, sondern eine Oder-Verknüpfung. Den ersten Ansatz liefert die Abfrage aus Bild 3, aber sie liefert immer noch nicht genau die gewünschten Datensätze, sondern alle Personen, die mindestens einen der beiden Skills aufweisen.
Das hilft insofern weiter, als schon einmal ein Abfrageergebnis vorliegt, das zumindest die gesuchten Datensätze enthält. Wie unterscheiden sich nun die „richtigen“ von den „falschen“ Datensätzen? Die richtigen kommen zweimal im Abfrageergebnis vor. Nun muss man nur noch alle Datensätze rauswerfen, die nicht zweimal auftauchen.
Dazu verwendet man eine Gruppierung und die Berechnung der Anzahl. Gehen Sie folgendermaßen vor:
-
Aktivieren Sie die Zeile Funktionen im Abfrage-Entwurf.
-
Ziehen Sie das Feld PersonID ein zweites Mal in das Entwurfsraster, blenden Sie es aus und legen Sie die Funktion Bedingung für dieses Feld fest.
-
Blenden Sie die Spalte Skills aus.
-
Legen Sie für die neue Spalte PersonID die Funktion Anzahl fest und stellen Sie als Kriterium für dieses Feld den Wert 2 ein. Das Ergebnis entnehmen Sie Bild 4.
Der Wechsel in die Datenblattansicht bestätigt die Annahme: Die Abfrage liefert das gewünschte Ergebnis.
Wie sieht es nun aus, wenn drei verschiedene Skills gefragt sind? Sie müssen dann die drei durch den Oder-Operator verknüpften Skills als Bedingung für das Feld Skill eintragen und das Kriterium für das Feld mit der Anzahl der gleichen Datensätze auf 3 einstellen. Besonders flexibel ist das natürlich nicht, und um diese Art der Abfrage dynamisch von einem Formular aus aufrufen zu können, ist noch einige Arbeit nötig.
Die besten Treffer
Bevor es an die Benutzeroberfläche geht, lernen Sie noch eine interessante Variante dieser Abfrage kennen: In manchen Fällen ist die Person mit bestimmten Fähigkeiten gar nicht vorhanden, oder man möchte vielleicht die treffendsten Teilnehmer auswählen. Wenn in dem Fall etwa vier Skills als Kriterium herhalten und das Abfrageergebnis die besten Treffer ausgeben soll, bauen Sie die Abfrage wie folgt um:
-
Geben Sie die gesuchten Skills wie gehabt durch Oder verknüpft als Kriterium für das Feld Skill ein.
-
Leeren Sie das Kriterium des Feldes mit der Anzahl der Datensätze und stellen Sie in der Zeile Sortierung den Wert Absteigend ein.
-
Lassen Sie dieses Feld nun im Abfrageergebnis anzeigen.
Das Ergebnis aus der umgestalteten Abfrage (Bild 5) überzeugt: Die Abfrage liefert zunächst die Volltreffer mit vier passenden Skills und anschließend die nächstbesten Treffer.
m:n-Suche im Formular
Bleibt noch die Aufgabe, die bisher recht statischen Beispiele flexibler zu gestalten und damit auch für Formulare zugänglich zu machen.
Eine wichtige Frage dreht sich um den Datentyp der Vergleichswerte: In den vorangegangenen Beispielen wurde jeweils das beschreibende Feld Skills der Tabelle tblSkills als Kriterium herangezogen. Damit lässt man die Möglichkeit offen, auch Platzhalter wie das Fragezeichen (?) und das Sternchen (*) einzusetzen. Andersherum gibt es nur eine begrenzte Menge von Kriterien – warum also nicht einfach diese zur Auswahl anbieten? Die zwei folgenden Beispiele greifen beide Möglichkeiten auf.
Suche nach Textkriterien
Im Falle der bereits diskutierten Textkriterien soll das Formular in der Endlosansicht alle gefundenen Personen, die mindestens ein Kriterium erfüllen, in der Reihenfolge mit den meisten Treffern anzeigen. In der Entwurfsansicht sieht ein solches Formular wie in Bild 6 aus.
Das obere Textfeld txtSuchkriterien dient zur Eingabe der durch Leerzeichen getrennten Skills. Beim Öffnen des Formulars soll die Abfrage aus Bild 5 als Datenherkunft dienen, damit alle Datensätze angezeigt werden.
Die Eingabe der gesuchten Skills in das Textfeld txtSuchbegriffe soll das Ergebnis aktualisieren. Der SQL-Ausdruck für die Eigenschaft Recordsource wird dabei entsprechend den eingegebenen Suchbegriffen neu zusammengestellt. Das Ganze sieht wie in Listing 1 aus.
Private Sub txtSuchbegriffe_AfterUpdate()
Dim strSuchbegriffe() As String
Dim i As Integer
Dim strKriterium As String
strSuchbegriffe = Split(Me!txtSuchbegriffe)
For i = LBound(strSuchbegriffe) To UBound(strSuchbegriffe)
strKriterium = strKriterium & "Or (tblSkills.Skill) = '" & strSuchbegriffe(
i) & "' "
Next i
strKriterium = Mid(strKriterium, 4)
Me.RecordSource = "SELECT tblPersonen.PersonID, tblPersonen.PersonName,
Count(tblPersonen.PersonID) AS AnzahlvonPersonID FROM tblSkills INNER JOIN (tblPersonen
INNER JOIN tblPersonenSkills ON tblPersonen.PersonID = tblPersonenSkills.PersonID)
ON tblSkills.SkillID = tblPersonenSkills.SkillID WHERE " & strKriterium & "
GROUP BY tblPersonen.PersonID, tblPersonen.PersonName ORDER BY
Count(tblPersonen.PersonID) DESC;"
End Sub
Die Split-Funktion ermittelt aus der im Textfeld txtSuchbegriffe enthaltenen Zeichenkette die einzelnen Suchbegriffe und speichert sie in dem String-Array strSuchbegriffe. Dessen Einträge durchläuft die folgende For-Next-Schleife, erstellt für jeden Suchbegriff einen Ausdruck der Form Or (tblSkills.Skill) = '<Suchbegriff>' und hängt diese aneinander. Nach dem Abtrennen des führenden OR fügt die Routine den Suchausdruck in die eigentliche Abfrage ein und weist diesen Ausdruck der Eigenschaft RecordSource des Formulars zu.
Bild 7 zeigt das Ergebnis einer Abfrage nach einem Mitarbeiter mit Office- und VB.NETKenntnissen.
Suche nach Schlüsseln
Im vorliegenden Fall wesentlich genauer und einfacher zu bedienen ist wohl eine Lösung, die alle vorhandenen Skills in Form von Kombinationsfeldern zur Auswahl anbietet. Auf diese Weise kann der Benutzer wesentlich schneller die gesuchten Skills auswählen und dabei außerdem keine Eingabefehler mehr machen.
Der Entwurf sieht ähnlich aus wie beim vorherigen Formular (Bild 8). Allerdings gibt es statt eines Textfeldes zur Eingabe beliebig vieler Suchbegriffe fünf Kombinationsfelder. Sie heißen cboSkill1 bis cboSkill5 und enthalten als Datensatzherkunft jeweils die Tabelle tblSkills. Damit sie nur den Skill und nicht die ID anzeigen, sind die Eigenschaften Spaltenanzahl und Spaltenbreiten auf die Werte 2 beziehungsweise 0cm eingestellt.
Die Datenherkunft des Formulars ist beim Öffnen des Formulars die gleiche wie beim vorherigen Formular, aber das Aktualisieren des Suchergebnisses funktioniert natürlich etwas anders. Die Prozeduren, die durch die Ereigniseigenschaften Nach Aktualisierung ausgelöst werden, rufen jeweils die Routine SucheAktualisieren auf (Listing 2). Die entsprechenden Ereignisprozeduren der anderen Kombinationsfelder sehen ähnlich aus.
Private Sub cboSkill1_AfterUpdate()
SucheAktualisieren
End Sub
Diese Routine durchläuft alle fünf Kombinationsfelder und prüft, ob sie einen Wert enthalten. Ist das der Fall, setzt die Routine die Werte zu einem Suchkriterium wie etwa (tblPersonenSkills. SkillID) = 1 Or (tblPersonenSkills.SkillID) = 2 Or (tblPersonenSkills.SkillID) = 3 zusammen.
Zusammenfassung
Mit den hier vorgestellten Techniken kann man Daten einer m:n-Beziehung nach bestimmten Werten der einen Seite der Beziehung durchsuchen. Das hilft in vielen Fällen weiter – egal ob Sie wie hier bestimmte Mitarbeiter oder eine Mahlzeit mit bestimmten Zutaten oder ein Fahrzeug mit Ihrer Wunschausstattung suchen.
Private Sub SucheAktualisieren()
Dim i As Integer
Dim strKriterium As String
For i = 1 To 5
If Not IsNull(Me("cboSkill" & i)) Then
strKriterium = strKriterium & "Or (tblPersonenSkills.SkillID) = " &
Me("cboSkill" & i) & " "
End If
Next i
strKriterium = Mid(strKriterium, 4)
Me.RecordSource = "SELECT tblPersonen.PersonID, tblPersonen.PersonName,
Count(tblPersonen.PersonID) AS AnzahlvonPersonID FROM tblPersonen INNER JOIN
tblPersonenSkills ON tblPersonen.PersonID = tblPersonenSkills.PersonID WHERE " &
strKriterium & " GROUP BY tblPersonen.PersonID, tblPersonen.PersonName ORDER BY
Count(tblPersonen.PersonID) DESC;"
End Sub